<?php

namespace app\admin\controller\dahui;

use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\Db;
use think\Exception;
use think\exception\PDOException;
use think\exception\ValidateException;


//use PhpOffice\PhpSpreadsheet\Writer\Xlsx;


/**
 * 订单管理
 *
 * @icon fa fa-circle-o
 */
class Dingdan extends Backend
{

    /**
     * Dingdan模型对象
     * @var \app\admin\model\dahui\Dingdan
     */
    protected $model = null;

    protected $searchFields = 'id,idnumber,taobaoname,kehu.name,kehu.mobile,kehu.bankcardnumber';

    protected $noNeedRight = ['islaokehu', 'plxrstatus1', 'plxrstatus2', 'plzcstatus0', 'plzcstatus1', 'plzcstatus2', 'changetstatus4', 'saveadd', 'import0', 'import1', 'import2','import3','import4','import5','import6','import7','addhis','tbyewu2', 'audit', 'xgjs', 'editkehunote', 'tbyewu', 'del1', 'edit1', 'edit2', 'edit3', 'edit4', 'edit5', 'edit6', 'edit7', 'cxselect', 'changetstatus', 'changetstatus2', 'changetstatus3', 'changexrstatus', 'changezcstatus', 'changetqzcstatus', 'allaudit'];

    protected $tuikuan = 0;
    protected $caozuo = 0;
    protected $audit = 0;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\dahui\Dingdan;
        $this->view->assign("businesstypeList", $this->model->getBusinesstypeList());
        $this->view->assign("auditstatusList", $this->model->getAuditstatusList());
        $this->view->assign("newkehuList", $this->model->getNewkehuList());
        $this->view->assign("preprocessingList", $this->model->getPreprocessingList());

        $this->assignconfig('audit', $this->auth->check('dahui/dingdan/audit'));
        $this->assignconfig('luru', $this->auth->check('dahui/dingdan/luru'));
        $this->assignconfig('caozuo', $this->auth->check('dahui/dingdan/caozuo'));
        $this->assignconfig('tuikuan', $this->auth->check('dahui/newtk/tuikuan'));

        $this->tuikuan = $this->auth->check('dahui/newtk/tuikuan');
        $this->caozuo = $this->auth->check('dahui/dingdan/caozuo');
        $this->audit = $this->auth->check('dahui/dingdan/audit');

        $dahui = $this->auth->check('dahui/dingdan/audit')
            && $this->auth->check('dahui/dingdan/luru') && $this->auth->check('dahui/dingdan/caozuo');
        $this->assignconfig('dahui', $dahui);
        $this->view->assign("dahui", $dahui);

        $minimumcardinalitys = [4000, 24140, 4927, 2420];

        $glist = \app\admin\model\dahui\Gongshi::all();
        foreach ($glist as $g) {
            if ($g['businesstype'] == '个税') {
                $minimumcardinalitys[0] = $g->minimumcardinality;
            } else if ($g['businesstype'] == '公积金') {
                $minimumcardinalitys[1] = $g->minimumcardinality;
            } else if ($g['businesstype'] == '社保') {
                $minimumcardinalitys[2] = $g->minimumcardinality;
            } else if ($g['businesstype'] == '补充公积金') {
                $minimumcardinalitys[3] = $g->minimumcardinality;
            }
        }

        $this->assignconfig('minimumcardinalitys', $minimumcardinalitys);
    }

    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */


    /**
     * 查看
     */
    public function index()
    {
        //当前是否为关联查询
        $this->relationSearch = true;
//        //设置过滤方法
        $this->request->filter(['strip_tags']);
        if ($this->request->isAjax()) {
            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            list($where, $sort, $order, $offset, $limit) = $this->buildparams();
            $total = $this->model
                ->with(['kehu'])
                ->where($where)
//                ->order($sort, $order)
                ->order('orderdate desc')
                ->count();

            $list = $this->model
                ->with(['kehu'])
                ->where($where)
//                ->order($sort, $order)
                ->order('orderdate desc')
                ->limit($offset, $limit)
                ->select();

            $list = collection($list)->toArray();
            if ($limit == 0) {
                //导出
                foreach ($list as &$row) {
                    $row = $this->importrow($row);
                }
            }

            $result = array("total" => $total, "rows" => $list);

            return json($result);
        }
        return $this->view->fetch();
    }

    function importstr($one)
    {

        return "\t" . $one;
    }

    function importrow($row)
    {

//        $row['idnumber'] = $this->importstr($row['idnumber']);
//        $row['paymentnumber'] = $this->importstr($row['paymentnumber']);
//        $row['paymentdate'] = $this->importstr($row['paymentdate']);
//        if (false === strpos($row['paymentdate'], '~')) {
//            $row['paymentdate'] =  $row['paymentdate'].'   ~';
//        }

        return $row;
    }

    /**
     * 添加
     */
    public function add()
    {
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                if ($params['paymentdate'] != $params['paymentdate2']) {
                    $params['paymentdate'] = $params['paymentdate'] . " ~ " . $params['paymentdate2'];
                }
                $params = $this->preExcludeFields($params);

                if ($this->dataLimit && $this->dataLimitFieldAutoFill) {
                    $params[$this->dataLimitField] = $this->auth->id;
                }
                $result = false;
                Db::startTrans();
                try {
                    //是否采用模型验证
                    if ($this->modelValidate) {
                        $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                        $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.add' : $name) : $this->modelValidate;
                        $this->model->validateFailException(true)->validate($validate);
                    }
                    $params['orderdate'] = date('Y-m-d H:i:s');

                    $total = $this->model
                        ->where('businesstype', $params['businesstype'])
                        ->where('idnumber', $params['idnumber'])
                        ->where('auditstatus', '未审核')
                        ->where('paymentdate', 'like', '%'.$params['paymentdate'].'%')
                        ->count();

                    if ($total > 0) {
                        $this->error(__('系统已录入同一客户同一业务相同的未审核缴纳年月订单!'));
                    }

                    $dates = explode(" ~ ", $params['paymentdate']);

                    $paymentdate = $dates[0];
                    $paymentdate2 = $paymentdate;
                    if (count($dates) > 1) {
                        $paymentdate2 = $dates[1];
                    }

                    if (isset($params['taobaoname'])) {
                        $params['taobaoname'] = trim($params['taobaoname']);
                    }

                    $yw = \app\admin\model\dahui\Yewu::where('businesstype', $params['businesstype'])
                        ->where('idnumber', $params['idnumber'])
                        ->find();
                    if ($yw) {
                        $total = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                            ->where('paymentdate', '>=', $paymentdate)
                            ->where('paymentdate', '<=', $paymentdate2)
                            ->where('refundornot', '')
                            ->count();

                        if ($total > 0) {
                            $this->error(__('系统已存在同一客户同一业务相同的缴纳年月!'));
                        }
                    }

                    $params = $this->setquanxianlog($params);

                    $result = $this->model->allowField(true)->save($params);

                    $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $params['idnumber']]);
                    if ($kehu) {
                        $kehu->name = $params['name'];
                        $kehu->mobile = $params['mobile'];
                        $kehu->save();
                    } else {
                        $kehu = new \app\admin\model\dahui\Kehu;
                        $result2 = $kehu->allowField(true)->save($params);
                    }

                    Db::commit();
                } catch (ValidateException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (PDOException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                if ($result !== false) {
//                    $this->success();
                    $this->error("已添加成功, 继续加油！", null, '', 1);
                } else {
                    $this->error(__('No rows were inserted'));
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }

        $Gongshi = new \app\admin\model\dahui\Gongshi;
        $companyList = collection($Gongshi->field('businesstype, name, minimumcardinality, proportion')->select())->toArray();
        $this->view->assign("companyList", $companyList);

        return $this->view->fetch();
    }

    //添加已使用ajax单独处理
    public function saveadd()
    {
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                if ($params['paymentdate'] != $params['paymentdate2']) {
                    $params['paymentdate'] = $params['paymentdate'] . " ~ " . $params['paymentdate2'];
                }
                $params = $this->preExcludeFields($params);

                if ($this->dataLimit && $this->dataLimitFieldAutoFill) {
                    $params[$this->dataLimitField] = $this->auth->id;
                }
                $result = false;
                Db::startTrans();
                try {
                    //是否采用模型验证
                    if ($this->modelValidate) {
                        $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                        $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.add' : $name) : $this->modelValidate;
                        $this->model->validateFailException(true)->validate($validate);
                    }
                    $params['orderdate'] = date('Y-m-d H:i:s');

                    $params['taobaoname'] = trim($params['taobaoname']);
                    if (isset($params['preprocessing']) && $params['preprocessing'] == '是') {
                        //预处理

                    } else if (isset($params['qztj']) && $params['qztj'] == '是'){
                        //强制添加，
                        //删除订单和对应的缴纳记录，重新添加订单

                    } else {
                        $total = $this->model
                            ->where('businesstype', $params['businesstype'])
                            ->where('idnumber', $params['idnumber'])
                            ->where('auditstatus', '未审核')
                            ->where('paymentdate', 'like', '%'.$params['paymentdate'].'%')
                            ->count();

                        if ($total > 0) {
                            $this->error(__('系统已录入同一客户同一业务相同的未审核缴纳年月订单!'));
                        }

                        $dates = explode(" ~ ", $params['paymentdate']);

                        $paymentdate = $dates[0];
                        $paymentdate2 = $paymentdate;
                        if (count($dates) > 1) {
                            $paymentdate2 = $dates[1];
                        }

                        $yw = \app\admin\model\dahui\Yewu::where('businesstype', $params['businesstype'])
                            ->where('idnumber', $params['idnumber'])
                            ->find();
                        if ($yw) {
                            $total = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                                ->where('paymentdate', '>=', $paymentdate)
                                ->where('paymentdate', '<=', $paymentdate2)
                                ->where('refundornot', '')
                                ->count();

                            if ($total > 0) {
                                $this->error(__('系统已存在同一客户同一业务相同的缴纳年月!'));
                            }
                        }
                    }
                    $params = $this->setquanxianlog($params);

                    $result = $this->model->allowField(true)->save($params);

                    if (isset($params['idnumber']) && strlen($params['idnumber']) > 10) {
                        $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $params['idnumber']]);
                        if ($kehu) {
                            $kehu->name = $params['name'];
                            $kehu->mobile = $params['mobile'];
                            $kehu->save();
                        } else {
                            $kehu = new \app\admin\model\dahui\Kehu;
                            $result2 = $kehu->allowField(true)->save($params);
                        }
                    }

                    Db::commit();
                } catch (ValidateException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (PDOException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                if ($result !== false) {
//                    $this->success();
//                    $this->error("已添加成功, 继续加油！", null, '', 1);
                    $this->success("已添加成功！");
                } else {
                    $this->error(__('No rows were inserted'));
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
    }
    /**
     * 编辑 订单管理
     */
    public function edit($ids = null)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            if (!in_array($row[$this->dataLimitField], $adminIds)) {
                $this->error(__('You have no permission'));
            }
        }
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {

                if (!isset($params['paymentdate'])) {
                    if (isset($params['agencyfees'])) {
                        $row->agencyfees = $params['agencyfees'];
                        $row = $this->setquanxianlog2($row);
                        $row->save();

                        $yw = \app\admin\model\dahui\Yewu::where('businesstype', $row['businesstype'])
                            ->where('idnumber', $row['idnumber'])->find();
                        if ($yw) {
                            $yw->agencyfees = $params['agencyfees'];
                            $yw->save();
                        }

                        $this->success('修改付代理费成功！');
                    } else if (isset($params['ordercomment'])) {
                        $row->ordercomment = $params['ordercomment'];
                        $row = $this->setquanxianlog2($row);
                        $row->save();
                        $this->success('修改订单备注成功！');
                    }
//                    else if (isset($params['paymentnumber'])) {
//                        $row->paymentnumber = $params['paymentnumber'];
//                        $row->save();
//
//                        //添加业务
//                        $yw = \app\admin\model\dahui\Yewu::where('businesstype', $row['businesstype'])
//                            ->where('idnumber', $row['idnumber'])
//                            ->find();
//                        if ($yw) {
//                            $yw->paymentnumber = $row['paymentnumber'];
//                            $yw->save();
//                        }
//
//                        $this->success('修改缴纳号码成功！');
//                    }
                }

                if ($row['auditstatus'] == '已审核') {
                    $this->error(__('已审核的订单不能修改！'));
                }

                if ($params['paymentdate'] != $params['paymentdate2']) {
                    $params['paymentdate'] = $params['paymentdate'] . " ~ " . $params['paymentdate2'];
                }
                $params = $this->preExcludeFields($params);
                $result = false;
                Db::startTrans();
                try {
                    //是否采用模型验证
                    if ($this->modelValidate) {
                        $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                        $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.edit' : $name) : $this->modelValidate;
                        $row->validateFailException(true)->validate($validate);
                    }

                    $total = $this->model
                        ->where('businesstype', $params['businesstype'])
                        ->where('idnumber', $params['idnumber'])
                        ->where('id', '<>', $row->id)
                        ->where('paymentdate', 'like', '%'.$params['paymentdate'].'%')
                        ->count();

                    if ($total > 0) {
                        $this->error(__('系统已存在同一客户同一业务相同的缴纳年月!'));
                    }

                    if (!isset($params['newkehu'])) {
                        $params['newkehu'] = "";
                    }
                    if (!isset($params['preprocessing'])) {
                        $params['preprocessing'] = "";
                    }

                    $params = $this->setquanxianlog($params);

                    $result = $row->allowField(true)->save($params);

                    $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $params['idnumber']]);
                    if ($kehu) {
                        $kehu->name = $params['name'];
                        $kehu->mobile = $params['mobile'];
                        $kehu = $this->setquanxianlog2($kehu);
                        $kehu->save();
                    } else {
                        $kehu = new \app\admin\model\dahui\Kehu;
                        $result2 = $kehu->allowField(true)->save($params);
                    }

                    Db::commit();
                } catch (ValidateException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (PDOException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                if ($result !== false) {
                    $this->success();
                } else {
                    $this->error(__('No rows were updated'));
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }

        $Gongshi = new \app\admin\model\dahui\Gongshi;
        $companyList = collection($Gongshi->field('businesstype, name, minimumcardinality, proportion')->select())->toArray();
        $this->view->assign("companyList", $companyList);

        $dates = explode(" ~ ", $row['paymentdate']);
        $paymentdate = $dates[0];
        $paymentdate2 = $paymentdate;
        if (count($dates) > 1) {
            $paymentdate2 = $dates[1];
        }

        $row['paymentdate'] = $paymentdate;
        $row['paymentdate2'] = $paymentdate2;

        $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $row['idnumber']]);
        if ($kehu) {
            $row['name'] = $kehu->name;
            $row['mobile'] = $kehu->mobile;
        }

        $companyList2 = collection($Gongshi->field('name')->where('businesstype', $row['businesstype'])->select())->toArray();
        $this->view->assign("companyList2", $companyList2);

        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 审核 订单管理
     */
    public function audit($ids = null)
    {
        $row = $this->model->get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($row['auditstatus'] == '已审核') {
            $this->error(__('已审核的订单不能修改！'));
        }

        $adminIds = $this->getDataLimitAdminIds();
        if (is_array($adminIds)) {
            if (!in_array($row[$this->dataLimitField], $adminIds)) {
                $this->error(__('You have no permission'));
            }
        }
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                if ($params['paymentdate'] < $params['paymentdate2']) {
                    $this->error(__('缴纳年月结束小于缴纳年月开始, 请重新设置！'));
                }

                if ($params['paymentdate'] != $params['paymentdate2']) {
                    $params['paymentdate'] = $params['paymentdate'] . " ~ " . $params['paymentdate2'];
                }
                $params = $this->preExcludeFields($params);
                $result = false;
                Db::startTrans();
                try {
                    //是否采用模型验证
                    if ($this->modelValidate) {
                        $name = str_replace("\\model\\", "\\validate\\", get_class($this->model));
                        $validate = is_bool($this->modelValidate) ? ($this->modelSceneValidate ? $name . '.edit' : $name) : $this->modelValidate;
                        $row->validateFailException(true)->validate($validate);
                    }


                    if (isset($params['preprocessing']) && $params['preprocessing'] == '是') {
                        $this->error(__('预处理的订单不能审核！'));
                    } else {
                        $params['preprocessing'] = '';
                    }

//                    $total = $this->model
//                        ->where('businesstype', $params['businesstype'])
//                        ->where('idnumber', $params['idnumber'])
//                        ->where('id', '<>', $row->id)
//                        ->where('paymentdate', $params['paymentdate'])
//                        ->count();
//
//                    if ($total > 0) {
//                        $this->error(__('系统已存在同一客户同一业务相同的缴纳年月!'));
//                    }

                    if (!isset($params['newkehu'])) {
                        $params['newkehu'] = "";
                    }
                    if (!isset($params['preprocessing'])) {
                        $params['preprocessing'] = "";
                    }

                    $params = $this->setquanxianlog($params);

                    $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $params['idnumber']]);
                    if ($kehu) {
                        $kehu->name = $params['name'];
                        $kehu->mobile = $params['mobile'];
                        $kehu = $this->setquanxianlog2($kehu);
                        $kehu->save();
                    } else {
                        $kehu = new \app\admin\model\dahui\Kehu;
                        $result2 = $kehu->allowField(true)->save($params);
                    }

                    //添加业务
                    $yw = \app\admin\model\dahui\Yewu::where('businesstype', $params['businesstype'])
                        ->where('idnumber', $params['idnumber'])
                        //            ->where('paymentnumber', '2019-05')    // 缴纳号码不唯一，可以更改
                        ->find();
                    if (!$yw) {
                        $yw = new \app\admin\model\dahui\Yewu();
                        $yw->idnumber = $params['idnumber'];
                        $yw->save();
                        $ywid = $yw->id;

                        $yw->businessnumber = "" . $ywid;
                    }

                    $yw->businesstype = $params['businesstype'];
                    $yw->idnumber = $params['idnumber'];
                    $yw->paymentnumber = $params['paymentnumber'];
                    $yw->agencyfees = $params['agencyfees'];
                    $yw->save();

                    $dates = explode(" ~ ", $params['paymentdate']);
                    $paymentdate = $dates[0];
                    $paymentdate2 = $paymentdate;
                    if (count($dates) > 1) {
                        $paymentdate2 = $dates[1];
                    }

                    for ($i = 0; $i < 48; $i++) {
                        $ny = new \app\admin\model\dahui\Nianyue;

                        $num = $ny->where('businessnumber', $yw->businessnumber)
                            ->where('paymentdate', $paymentdate)
                            ->where('refundornot', '<>', "已退款")
                            ->count();
                        if ($num > 0) {
                            //已有记录审核不通过！
                            $this->error('已缴纳，审核失败！');
                            break;
                        }

                        $ny->businessnumber = $yw->businessnumber;
                        $ny->paymentdate = $paymentdate;
                        $ny->payingcompany = $params['payingcompany'];
                        $ny->paymentbase = $params['paymentbase'];
                        $ny->paymentamount = $params['paymentamount'];
                        $ny->taobaoname = $params['taobaoname'];

                        $ny = $this->setquanxianlog2($ny);
                        $ny->luruman = $row['luruman'];

                        if ($i == 0) {
                            $ny->dingdan_id = $row->id;
                        }
//            $ny->newjoin_note = ; 新入备注
//            $ny->zc_note = ; 转出备注
                        $ny->save();

                        $paymentdate = date("Y-m", strtotime("+1 months", strtotime($paymentdate)));
                        if ($paymentdate > $paymentdate2) {
                            break;
                        }
                    }

                    $params['auditstatus'] = '已审核';
                    $result = $row->allowField(true)->save($params);

                    Db::commit();
                } catch (ValidateException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (PDOException $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                } catch (Exception $e) {
                    Db::rollback();
                    $this->error($e->getMessage());
                }
                if ($result !== false) {
                    $this->success();
                } else {
                    $this->error(__('No rows were updated'));
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }

        $Gongshi = new \app\admin\model\dahui\Gongshi;
        $companyList = collection($Gongshi->field('businesstype, name, minimumcardinality, proportion')->select())->toArray();
        $this->view->assign("companyList", $companyList);

        $dates = explode(" ~ ", $row['paymentdate']);
        $paymentdate = $dates[0];
        $paymentdate2 = $paymentdate;
        if (count($dates) > 1) {
            $paymentdate2 = $dates[1];
        }

        $row['paymentdate'] = $paymentdate;
        $row['paymentdate2'] = $paymentdate2;

        $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $row['idnumber']]);
        if ($kehu) {
            $row['name'] = $kehu->name;
            $row['mobile'] = $kehu->mobile;
        }

        $companyList2 = collection($Gongshi->field('name')->where('businesstype', $row['businesstype'])->select())->toArray();
        $this->view->assign("companyList2", $companyList2);

        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 批量审核 订单管理
     */
    public function allaudit($ids = null)
    {
        $list = $this->model
            ->where('id', 'in', $ids)
//            ->where('preprocessing', '<>', '是')
            ->select();

        foreach ($list as $row) {
            if ($row->preprocessing == '是') {
                $this->error('预处理的订单不能审核！_' . $row->id);
                return;
            }
        }

//        if (isset($params['preprocessing']) && $params['preprocessing'] == '是') {
//            $this->error(__('预处理的订单不能审核！'));
//        }

        foreach ($list as $row) {
            if ($row['auditstatus'] == '已审核') {
                continue;
            }

            //已有订单，但是缴纳记录为已退款，又重新下订单
//            $total = $this->model
//                ->where('businesstype', $row['businesstype'])
//                ->where('idnumber', $row['idnumber'])
//                ->where('id', '<>', $row->id)
//                ->where('paymentdate', $row['paymentdate'])
//                ->count();
//
//            if ($total > 0) {
//                continue;
//            }

            //添加业务
            $yw = \app\admin\model\dahui\Yewu::where('businesstype', $row['businesstype'])
                ->where('idnumber', $row['idnumber'])
                ->find();
            if (!$yw) {
                $yw = new \app\admin\model\dahui\Yewu();
                $yw->idnumber = $row['idnumber'];
                $yw->save();
                $ywid = $yw->id;

                $yw->businessnumber = "" . $ywid;
            }

            $yw->businesstype = $row['businesstype'];
            $yw->idnumber = $row['idnumber'];
            $yw->paymentnumber = $row['paymentnumber'];
            $yw->agencyfees = $row['agencyfees'];
            $yw->save();

            $dates = explode(" ~ ", $row['paymentdate']);
            $paymentdate = $dates[0];
            $paymentdate2 = $paymentdate;
            if (count($dates) > 1) {
                $paymentdate2 = $dates[1];
            }

            for ($i = 0; $i < 48; $i++) {
                $ny = new \app\admin\model\dahui\Nianyue;

                $num = $ny->where('businessnumber', $yw->businessnumber)
                    ->where('paymentdate', $paymentdate)
                    ->where('refundornot', '<>', '已退款')
                    ->count();
                if ($num > 0) {
                    //已有记录审核不通过！
                    $this->error('已缴纳，审核失败！');
                    break;
                }

                $ny->businessnumber = $yw->businessnumber;
                $ny->paymentdate = $paymentdate;
                $ny->payingcompany = $row['payingcompany'];
                $ny->paymentbase = $row['paymentbase'];
                $ny->paymentamount = $row['paymentamount'];
                $ny->taobaoname = $row['taobaoname'];
                $ny = $this->setquanxianlog2($ny);
                $ny->luruman = $row['luruman'];
                if ($i == 0) {
                    $ny->dingdan_id = $row->id;
                }
                $ny->save();

                $paymentdate = date("Y-m", strtotime("+1 months", strtotime($paymentdate)));
                if ($paymentdate > $paymentdate2) {
                    break;
                }
            }

            $row = $this->setquanxianlog2($row);
            $row['auditstatus'] = '已审核';
            $row->save();
        }

        $this->success();

    }


    /**
     * 删除
     */
    public function del($ids = "")
    {
        if ($ids) {

            $row = $this->model->get($ids);
            if ($row['auditstatus'] == '已审核') {
                $this->error(__('已审核的订单不能删除！'));
            }

            $params = $row->toArray();
            $dingdan2 = new \app\admin\model\dahui\Dingdan2();
            $params['delman'] = $this->auth->nickname;
            unset($params['create_time']);
            $dingdan2->allowField(true)->save($params);

            $pk = $this->model->getPk();
            $adminIds = $this->getDataLimitAdminIds();
            if (is_array($adminIds)) {
                $this->model->where($this->dataLimitField, 'in', $adminIds);
            }
            $list = $this->model->where($pk, 'in', $ids)->select();

            $count = 0;
            Db::startTrans();
            try {
                foreach ($list as $k => $v) {
                    $count += $v->delete();
                }
                Db::commit();
            } catch (PDOException $e) {
                Db::rollback();
                $this->error($e->getMessage());
            } catch (Exception $e) {
                Db::rollback();
                $this->error($e->getMessage());
            }
            if ($count) {
                $this->success();
            } else {
                $this->error(__('No rows were deleted'));
            }
        }
        $this->error(__('Parameter %s can not be empty', 'ids'));
    }

    public function del1($ids = "")
    {
        if ($ids) {

            $row = \app\admin\model\dahui\Nianyue::get($ids);
            if (!$row) {
                $this->error(__('No Results were found'));
            }

            $params = $row->toArray();
            $nianyue2 = new \app\admin\model\dahui\Nianyue2();
            $params['delman'] = $this->auth->nickname;
            if (empty($params['earlytransfer'])) {
                unset($params['earlytransfer']);
            }
            unset($params['create_time']);
            $nianyue2->allowField(true)->save($params);

            $row->delete();
            $this->success();
        }
        $this->error(__('Parameter %s can not be empty', 'ids'));
    }

    /**
     * 编辑 缴纳明细
     */
    public function edit1($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        $this->view->assign("refundornotList", $row->getRefundornotList());

        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            $yw = \app\admin\model\dahui\Yewu::get(['businessnumber' => $row->businessnumber]);
            if ($params && $yw) {

                if (isset($params['kehu.mobile'])) {
                    $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $yw->idnumber]);
                    $kehu->mobile = $params['kehu.mobile'];
                    $kehu = $this->setquanxianlog2($kehu);
                    $kehu->save();
                    $this->success('客户电话修改成功');
                } else if (isset($params['paymentnumber'])) {
                    $yw->paymentnumber = $params['paymentnumber'];
                    $yw = $this->setquanxianlog2($yw);
                    $yw->save();
                    $this->success('缴纳号码修改成功');
                } else if (isset($params['refundornot'])) {
                    $row->refundornot = $params['refundornot'];
                    $row = $this->setquanxianlog2($row);
                    $row->save();
                    $this->success('是否退款修改成功');
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 编辑 续费提醒
     */
    public function edit2($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            $yw = \app\admin\model\dahui\Yewu::get(['businessnumber' => $row->businessnumber]);
            if ($params && $yw) {
                $yw->agencyfees = $params['agencyfees'];
                $yw->save();
                $this->success('续费客户修改成功');
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 编辑 续费客户
     */
    public function edit3($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            $yw = \app\admin\model\dahui\Yewu::get(['businessnumber' => $row->businessnumber]);
            if ($params && $yw) {
                $yw->agencyfees = $params['agencyfees'];
                $yw->save();
                $this->success('续费客户修改成功');
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 编辑 新入客户
     */
    public function edit4($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }


        $this->view->assign("newjoinStatusList", $row->getNewjoinStatusList());

        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");

            if ($params) {

                $row = $this->setquanxianlog2($row);
                if (isset($params['newjoin_note'])) {
                    $row->newjoin_note = $params['newjoin_note'];
                    $row->save();
                    $this->success('新入状态修改成功');
                }
                if (isset($params['paymentnumber'])) {
                    $yw = \app\admin\model\dahui\Yewu::get(['businessnumber' => $row->businessnumber]);
                    $yw->paymentnumber = $params['paymentnumber'];
                    $yw->save();
                    $this->success('缴纳号码修改成功');
                }
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 编辑 转出客户
     */
    public function edit5($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        $this->view->assign("earlytransferList", $row->getEarlytransferList());
        $this->view->assign("zcStatusList", $row->getZcStatusList());

        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            $row = $this->setquanxianlog2($row);
            if ($params) {
                $msg = '转出信息修改成功';
                if (isset($params['zc_note'])) {
                    $row->zc_note = $params['zc_note'];
                }
                if (isset($params['earlytransfer'])) {
                    $row->earlytransfer = $params['earlytransfer'];
                }
                if (isset($params['zc_status'])) {
                    $row->zc_status = $params['zc_status'];
                    $msg = '转出信息修改成功2';
                }
                $row->save();
                $this->success($msg);
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 编辑 退款客户
     */
    public function edit6($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($this->auth->name == 'Admin') {
            $this->view->assign("refundornotList", $row->getRefundornotList_foradmin());
        } else {
            $this->view->assign("refundornotList", $row->getRefundornotList());
        }

        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");

            if ($params) {
                $row->refundornot = $params['refundornot'];
                $row->save();
                $this->success('退款状态修改成功');
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 编辑 问题客户
     */
    public function edit7($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");

            if ($params) {
                $row->wenti_note = $params['wenti_note'];
                $row->save();
                $this->success('问题备注修改成功');
            }
            $this->error(__('Parameter %s can not be empty', ''));
        }
        $this->view->assign("row", $row);
        return $this->view->fetch();
    }

    /**
     * 编辑 客户电话
     */
    public function editkehumobile()
    {
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $idnumber = $params['idnumber'];
                $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $idnumber]);
                $kehu->note = $params['note'];
                $kehu->save();
                $this->success('客户备注修改成功');
            }
            $this->error(__('Parameter %s can not be empty', ''));
        } else {
            $idnumber = $this->request->request('idnumber');
            $kehu = \app\admin\model\dahui\Kehu::where('idnumber', $idnumber)
                ->field('note')
                ->find();
            $row = [];
            $row['note'] = $kehu['note'];
            $row['idnumber'] = $idnumber;
            $this->view->assign("row", $row);
        }

        return $this->view->fetch();
    }

    /**
     * 编辑 客户备注
     */
    public function editkehunote()
    {
        if ($this->request->isPost()) {
            $params = $this->request->post("row/a");
            if ($params) {
                $idnumber = $params['idnumber'];
                $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $idnumber]);
                $kehu->note = $params['note'];
                $kehu->save();
                $this->success('客户备注修改成功');
            }
            $this->error(__('Parameter %s can not be empty', ''));
        } else {
            $idnumber = $this->request->request('idnumber');
            $kehu = \app\admin\model\dahui\Kehu::where('idnumber', $idnumber)
                ->field('note')
                ->find();
            $row = [];
            $row['note'] = $kehu['note'];
            $row['idnumber'] = $idnumber;
            $this->view->assign("row", $row);
        }

        return $this->view->fetch();
    }

    //http://dahui.test/admin/dahui/dingdan/getkehu?idnumber=110105197110300819
    public function getkehu()
    {
        $idnumber = $this->request->request('idnumber');
        if (!$idnumber) {
            $this->error('缺少身份证号！');
        }

        $kehu = \app\admin\model\dahui\Kehu::get(['idnumber' => $idnumber]);
        if (!$kehu) {
            $this->error('找不到对应的客户！');
        }
        $yw = \app\admin\model\dahui\Yewu::where('idnumber', $idnumber)
            ->field('id,paymentnumber')
            ->find();

        $kehu = $kehu->toArray();
        if ($yw) {
            $kehu['paymentnumber'] = $yw->paymentnumber;
        }

        $this->success('查找成功', null, $kehu);//code=1 //和Api里的返回方式不一样
    }

    //http://dahui.test/admin/dahui/dingdan/tbyewu?taobaoname=-jjjj-
    //显示此淘宝号，办理过的业务
    public function tbyewu()
    {
        $taobaoname = $this->request->request('taobaoname', '');
        $idnumber = $this->request->request('idnumber', '');

        if ($this->request->isAjax()) {

            $model = new \app\admin\model\dahui\Yewu;
            $list = [];
            $list2 = [];//无记录，只有业务

            if (!empty($taobaoname)) {
                $list0 = \app\admin\model\dahui\Dingdan::where('taobaoname', $taobaoname)
                    ->field('idnumber')
                    ->group("idnumber")
                    ->select();
                foreach ($list0 as $row0) {

                    $ywlist = \app\admin\model\dahui\Yewu::query("SELECT `id`,`businessnumber`,`businesstype`,`idnumber`,`paymentnumber`,`agencyfees` FROM `fa_dahui_yewu` WHERE  `idnumber` = '{$row0['idnumber']}' ORDER BY CONVERT(`businesstype` USING gbk)");

                    foreach ($ywlist as &$yw) {
                        $yw['kehu'] = \app\admin\model\dahui\Kehu::where('idnumber', $yw['idnumber'])
                            ->field('name,mobile')
                            ->find()->toArray();

                        $nianyue = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                            ->where('refundornot', '<>', "已退款")
                            ->order('paymentdate', 'desc')
                            ->field('paymentdate,payingcompany,paymentbase,paymentamount,refundornot,zc_status,taobaoname')
                            ->limit(1)
                            ->find();

                        if (!$nianyue) {
//                            $list2[] = $yw;
                            $list[] = $yw;
                            continue;
                        }
                        $yw['taobaoname'] = $nianyue->taobaoname;
                        $yw['nianyue'] = $nianyue->toArray();

                        $yw['zc_log'] = '无转出记录';

                        $zc_nianyue = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                            ->where('zc_status', "已办理")
                            ->order('paymentdate', 'desc')
                            ->field('paymentdate')
                            ->limit(1)
                            ->find();
                        if ($zc_nianyue) {
                            $yw['zc_log'] = date("Y-m", strtotime("+1 months", strtotime($zc_nianyue['paymentdate'])))." 转出";
                        }

                        $list[] = $yw;
                    }
                }

            }

            if (!empty($idnumber)) {
//            $this->error('无效的淘宝号！');
                $ywlist = \app\admin\model\dahui\Yewu::query("SELECT `id`,`businessnumber`,`businesstype`,`idnumber`,`paymentnumber`,`agencyfees` FROM `fa_dahui_yewu` WHERE  `idnumber` = '{$idnumber}' ORDER BY CONVERT(`businesstype` USING gbk)");

                foreach ($ywlist as &$yw) {
                    $yw['kehu'] = \app\admin\model\dahui\Kehu::where('idnumber', $yw['idnumber'])
                        ->field('name,mobile')
                        ->find()->toArray();

                    $nianyue = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                        ->where('refundornot', '<>', "已退款")
                        ->order('paymentdate', 'desc')
                        ->field('paymentdate,payingcompany,paymentbase,paymentamount,refundornot,zc_status,taobaoname')
                        ->limit(1)
                        ->find();

                    if (!$nianyue) {
                        $list[] = $yw;
                        continue;
                    }
                    $yw['taobaoname'] = $nianyue->taobaoname;
                    $yw['nianyue'] = $nianyue->toArray();

                    $yw['zc_log'] = '无转出记录';

                    $zc_nianyue = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                        ->where('zc_status', "已办理")
                        ->order('paymentdate', 'desc')
                        ->field('paymentdate')
                        ->limit(1)
                        ->find();
                    if ($zc_nianyue) {
                        $yw['zc_log'] = date("Y-m", strtotime("+1 months", strtotime($zc_nianyue['paymentdate'])));
                    }

                    $list[] = $yw;
                }
            }

//            $sql = "select yw.businesstype,yw.idnumber,kehu.name,kehu.mobile,yw.paymentnumber,yw.agencyfees
//	from fa_dahui_yewu yw left join fa_dahui_kehu kehu on yw.idnumber=kehu.idnumber
//	where yw.idnumber in (select idnumber from fa_dahui_dingdan where taobaoname='黄时龙2184438349') order by yw.idnumber asc,yw.businesstype asc";
//            $list = Db::query($sql);

            $total = count($list);
            if ($total == 0) {
                $total = count($list2);
                $list = $list2;
            }
            $result = array("total" => $total, "rows" => $list);
            return json($result);
        } else {
            //这一招怪异么？
            $this->assignconfig('taobaoname', $taobaoname);
            $this->assignconfig('idnumber', $idnumber);
        }

        return $this->view->fetch();
    }

    //淘宝关联业务，加入
    public function addhis($ids)
    {
        if ($this->request->isPost()) {

            $yw = \app\admin\model\dahui\Yewu::where('id', $ids)
                ->field('id,businessnumber,businesstype,idnumber,paymentnumber,agencyfees')
                ->find();

            if ($yw) {
                $yw = $yw->toArray();
                $yw['kehu'] = \app\admin\model\dahui\Kehu::where('idnumber', $yw['idnumber'])
                    ->field('name,mobile')
                    ->find()->toArray();

                $nianyue = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                    ->where('refundornot', '<>', "已退款")
                    ->order('paymentdate', 'desc')
                    ->field('paymentdate,payingcompany,paymentbase,paymentamount,refundornot,zc_status,taobaoname')
                    ->limit(1)
                    ->find();

                if ($nianyue) {
                    $yw['nianyue'] = $nianyue->toArray();
                }

                $yw['zc_log'] = '无转出记录';

                $zc_nianyue = \app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                    ->where('zc_status', "已办理")
                    ->order('paymentdate', 'desc')
                    ->field('paymentdate')
                    ->limit(1)
                    ->find();
                if ($zc_nianyue) {
                    $yw['zc_log'] = $zc_nianyue['paymentdate'];
                }

                $this->success('success', null, $yw);
            }
        }
        $this->error(__('没有选中历史业务'));

    }

    //历史业务
    public function tbyewu2($idnumber)
    {
        if ($this->request->isAjax()) {

            $ywlist = \app\admin\model\dahui\Yewu::where('idnumber', $idnumber)
                ->field('id,businessnumber, businesstype')
                ->select();

            $ywlist = collection($ywlist)->toArray();
            $list = [];
            foreach ($ywlist as &$yw) {
                //select `paymentdate`,`payingcompany` from fa_dahui_nianyue where id in  (select max(id) from fa_dahui_nianyue WHERE  `businessnumber` = '14b2f8b3-bbcb-4e62-8202-99ffa34b6ab7'  group by payingcompany)
                $sql = "select id, `paymentdate`,`payingcompany` from fa_dahui_nianyue where id in  (select max(id) from fa_dahui_nianyue WHERE  `businessnumber` = '{$yw['businessnumber']}'  group by payingcompany)";
                $nylist = Db::query($sql);
                foreach ($nylist as $ny) {
                    $ny['businesstype'] = $yw['businesstype'];
                    $list[] = $ny;
                }
            }

            $result = array("total" => count($list), "rows" => $list);
            return json($result);
        }
        $this->error(__('没有记录'));

    }

    //判断是否是老客户
    public function islaokehu($idnumber, $businesstype) {

        $laokehu = 0;//初为新客户
        if ($idnumber && $businesstype) {
            $paymentdate = date("Y-m");
            $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));
            //上个月有记录就是老客户
            $sql1 = "select count(*) as num ";
            $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
                . "inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber "
                . "where ny.paymentdate = '{$paymentdate_pre}' and yw.businesstype = '{$businesstype}'";
            $sql .= " and yw.idnumber = '" . $idnumber . "' ";
            $sql .= " and ny.refundornot <> '已退款' and ny.zc_status <> '已办理'";
            $sqlXX = $sql1 . $sql;
            $total = Db::query($sqlXX);
            $total = $total[0]['num'];
            if ($total > 0) {
                $laokehu = 1;
            }
        }

        $this->success('success', null, $laokehu);
    }

    /**
     * 缴纳明细
     */
    public function index1()
    {
        if ($this->request->isAjax()) {
            $model = new \app\admin\model\dahui\Yewu;

            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);

            $offset = $this->request->get("offset", 0);
            $limit = $this->request->get("limit", 0);

            $idnumber = "";
            $businesstype = "";
            if (isset($filter['idnumber'])) {
                $idnumber = $filter['idnumber'];
            }
            if (isset($filter['businesstype'])) {
                $businesstype = $filter['businesstype'];
            }

            if (empty($idnumber)) {
                return json(array("total" => 0, "rows" => array(), "idnumber" => $idnumber, "note" => ''));
            }
            //这个也可以js要求refundornot这个字段不能为空
//            $sql = 'select a.*, k.id as keheid, k.name as kehename, k.mobile as kehemobile, k.bankcardnumber as kehebankcardnumber'
//                    .', k.note as note from fa_dahui_yewu a LEFT JOIN fa_dahui_kehu k on k.idnumber = a.idnumber where a.idnumber ='. $idnumber;
//            $list1 = Db::query($sql);

            $list = [];
            if (strlen($businesstype) > 1) {
                $ywlist = \app\admin\model\dahui\Yewu::where('idnumber', $idnumber)
                    ->where('businesstype', $businesstype)
                    ->field('id,businessnumber,businesstype,idnumber,paymentnumber,agencyfees')
                    ->select();
            } else {
                $ywlist = \app\admin\model\dahui\Yewu::where('idnumber', $idnumber)
                    ->field('id,businessnumber,businesstype,idnumber,paymentnumber,agencyfees')
                    ->select();
            }

            $kehu = \app\admin\model\dahui\Kehu::where('idnumber', $idnumber)
                ->field('name,mobile,note')
                ->find()->toArray();

            foreach ($ywlist as &$yw) {
                $nlist = collection(\app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
//                    ->where('refundornot', '<>', "已退款")
//                    ->where('newjoin_status', '<>', "办理失败")//新入客户办理失败后，在缴纳明细中是不能显示
                    ->order('paymentdate', 'desc')
                    ->select())->toArray();

                foreach ($nlist as $nianyue) {
                    $nianyue['kehu'] = $kehu;
                    $nianyue['idnumber'] = $idnumber;
                    $nianyue['businesstype'] = $yw['businesstype'];
                    $nianyue['paymentnumber'] = $yw['paymentnumber'];

                    if ($limit == 0) {
                        //导出
//                        $nianyue = $this->importrow($nianyue);
                    }


                    $list[] = $nianyue;
                }
            }

            if ($kehu['note'] == null) {
                $kehu['note'] = "";
            }

            $result = array("total" => count($list), "rows" => $list, "idnumber" => $idnumber, "note" => $kehu['note']);

            return json($result);
        }

        return $this->view->fetch();
    }

    /**
     * 续费提醒
     * 1. 上月有缴费记录，本月没有缴费记录，且没有本月缴费的未审核订单
     * 且上月缴费记录 转出状态不是已办理
     *
     */
    public function index2()
    {
        if ($this->request->isAjax()) {
            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);

            $offset = $this->request->get("offset", 0);
            $limit = $this->request->get("limit", 0);

            $idnumber = "";
            $paymentnumber = "";
            $businesstype = "";
            $paymentdate = "";
            $payingcompany = "";
            $taobaoname = "";
            if (isset($filter['idnumber'])) {
                $idnumber = $filter['idnumber'];
            }
            if (isset($filter['taobaoname'])) {
                $taobaoname = $filter['taobaoname'];
            }
            if (isset($filter['businesstype'])) {
                $businesstype = $filter['businesstype'];
            }
            if (isset($filter['paymentdate'])) {
                $paymentdate = $filter['paymentdate'];
            }

            if (isset($filter['payingcompany'])) {
                $payingcompany = $filter['payingcompany'];
            }
            if (isset($filter['paymentnumber'])) {
                $paymentnumber = $filter['paymentnumber'];
            }

            if (empty($paymentdate)) {
                $result = array("total" => 0, "rows" => array());
                return json($result);
            }
            $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));

            $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
//            $sql0 .= ", (select count(*) from fa_dahui_dingdan d where d.businesstype=yw.businesstype and d.idnumber=yw.idnumber and d.auditstatus='未审核'
//and d.paymentdate= '" . $paymentdate_pre . "') n0,";//确定
//            $sql0 .= " (select count(1) from fa_dahui_dingdan where businesstype=yw.businesstype and idnumber=yw.idnumber and auditstatus='未审核') n1 ";//可能录入
            $sql1 = "select count(*) as num ";
            $sql2 = "select sum(ny.paymentamount) as num ";
            $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
                . " inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber"
                . " where ny.paymentdate = '" . $paymentdate_pre . "' ";

            if (!empty($businesstype)) {
                $sql .= " and yw.businesstype = '" . $businesstype . "' ";
            }
            if (!empty($idnumber)) {
                $sql .= " and yw.idnumber = '" . $idnumber . "' ";
            }

            if (!empty($taobaoname)) {
                $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
            }

            if (!empty($payingcompany)) {
                $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
            }
            if (!empty($paymentnumber)) {
                $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
            }

            $sql .= " and ny.refundornot <> '已退款' and ny.zc_status <> '已办理' and ny.earlytransfer <> '是' ";//

//            $paymentdate_next = date("Y-m", strtotime("+1 months", strtotime($paymentdate)));
//上个月交了，这个月没有交
            $sql .= " and not exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '"
                . $paymentdate . "' and refundornot <> '已退款') ";// order by yw.idnumber asc

            //且没有订单
//             $sql .= " and not exists(select id from fa_dahui_dingdan where idnumber=yw.idnumber and businesstype=yw.businesstype and paymentdate like '%"
//                 . $paymentdate . "%' and auditstatus='未审核' ) ";
            //<-----
            //查询当月未审核的订单
            $sql2 = "select id, idnumber, businesstype, paymentdate, create_time from fa_dahui_dingdan where paymentdate like '%"
                . $paymentdate . "%' and auditstatus='未审核' and idnumber <> '' and idnumber <> '##' ";
            $list2 = Db::query($sql2);
            //----->
//            $total = Db::query($sql1 . $sql);
//            $money = Db::query($sql2 . $sql);
//            if ($limit > 0) {
//                $sql .= " limit " . $limit . " offset " . $offset;
//            }
            $sqlxx = $sql0 . $sql;
            $list = Db::query($sqlxx);
            $listR = [];
            $money = 0;
            $total = 0;
            $index = 0;
            foreach ($list as &$row) {
                $find = false;
                foreach ($list2 as $row2) {
                    if ($row['idnumber'] == $row2['idnumber'] && $row['businesstype'] == $row2['businesstype']) {
                        $find = true;
                        break;
                    }
                }
                if ($find) {
                    continue;
                }
                $money += intval($row['paymentamount']);
                $total++;
                //提醒年月
                $row['paymentdate2'] = date("Y-m", strtotime("+1 months", strtotime($row['paymentdate'])));
                if ($index >= $offset && count($listR) <= $limit) {
                    $listR[] = $row;
                }
                $index++;
            }

            $result = array("total" => $total, "rows" => $listR, "extend" => ['money' => sprintf("%.2f", $money)]);

            return json($result);
        }
        return $this->view->fetch();
    }

    /**
     * 续费客户
     * 1. 上月有缴费记录，本月有缴费记录
     *
     * 续费客户，去除当月订单为新客户的；去除上月 zc_status==已办理
     */
    public function index3()
    {
        if ($this->request->isAjax()) {
            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);

            $offset = $this->request->get("offset", 0);
            $limit = $this->request->get("limit", 0);

            $idnumber = "";
            $paymentnumber = "";
            $businesstype = "";
            $paymentdate = "";
            $payingcompany = "";
            $taobaoname = "";
            if (isset($filter['idnumber'])) {
                $idnumber = $filter['idnumber'];
            }
            if (isset($filter['taobaoname'])) {
                $taobaoname = $filter['taobaoname'];
            }
            if (isset($filter['businesstype'])) {
                $businesstype = $filter['businesstype'];
            }
            if (isset($filter['paymentdate'])) {
                $paymentdate = $filter['paymentdate'];
            }

            if (isset($filter['payingcompany'])) {
                $payingcompany = $filter['payingcompany'];
            }
            if (isset($filter['paymentnumber'])) {
                $paymentnumber = $filter['paymentnumber'];
            }

            if (empty($paymentdate)) {
                $result = array("total" => 0, "rows" => array());
                return json($result);
            }

            $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
            $sql1 = "select count(*) as num ";
            $sql2 = "select sum(ny.paymentamount) as num ";
            $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
                . " inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber"
                . " left JOIN fa_dahui_dingdan d on d.id = ny.dingdan_id "
                . " where ny.paymentdate = '" . $paymentdate . "' ";

            if (!empty($businesstype)) {
                $sql .= " and yw.businesstype = '" . $businesstype . "' ";
            }
            if (!empty($idnumber)) {
                $sql .= " and yw.idnumber = '" . $idnumber . "' ";
            }
            if (!empty($taobaoname)) {
                $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
            }
            if (!empty($payingcompany)) {
                $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
            }
            if (!empty($paymentnumber)) {
                $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
            }

//            $sql .= " and ny.refundornot <> '已退款' and ny.zc_status <> '已办理'";
            //无论本月是否转出都要有记录
            $sql .= " and ny.refundornot = '' ";

            $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));

//            $sql .= " and exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '"
//                . $paymentdate_pre . "' and payingcompany = ny.payingcompany and refundornot <> '已退款' and zc_status <> '已办理')";
            $sql .= " and exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '"
                . $paymentdate_pre . "' and payingcompany = ny.payingcompany and refundornot <> '已退款' and zc_status <> '已办理' )";

            $sql .= " and ( ny.dingdan_id = 0 or d.newkehu <> '是' ) ";


            $total = Db::query($sql1 . $sql);
            $money = Db::query($sql2 . $sql);
            if ($limit > 0) {
                $sql .= " limit " . $limit . " offset " . $offset;
            }
            $sqlXX = $sql0 . $sql;
            $list = Db::query($sqlXX);

            $result = array("total" => $total[0]['num'], "rows" => $list, "extend" => ['money' => sprintf("%.2f", $money[0]['num'])]);

            return json($result);
        }
        return $this->view->fetch();
    }

    /**
     * 新入客户  
     *  1. 上个月没有年月记录，这个月有的 （无论是否订单标记为新客户）
     * 或
     *  2. 订单标记为新客户的。（上个月可能有年月记录）
     *  3. 上月 zc_status == 已办理
     * 2->1
     * 年月需关联到订单, 只需要保证本月和上月的年月记录能关联到订单就好
     */
    public function index4()
    {
        if ($this->request->isAjax()) {
            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);

            $offset = $this->request->get("offset", 0);
            $limit = $this->request->get("limit", 0);

            $idnumber = "";
            $paymentnumber = "";
            $businesstype = "";
            $paymentdate = "";
            $newjoin_status = "";
            $payingcompany = "";
            $taobaoname = "";
            if (isset($filter['idnumber'])) {
                $idnumber = $filter['idnumber'];
            }

            if (isset($filter['taobaoname'])) {
                $taobaoname = $filter['taobaoname'];
            }

            if (isset($filter['businesstype'])) {
                $businesstype = $filter['businesstype'];
            }
            if (isset($filter['paymentdate'])) {
                $paymentdate = $filter['paymentdate'];
            }

            if (isset($filter['newjoin_status'])) {
                $newjoin_status = $filter['newjoin_status'];
            }
            if (isset($filter['payingcompany'])) {
                $payingcompany = $filter['payingcompany'];
            }
            if (isset($filter['paymentnumber'])) {
                $paymentnumber = $filter['paymentnumber'];
            }

            if (empty($paymentdate)) {
                $result = array("total" => 0, "rows" => array());
                return json($result);
            }

            //找到订单//

            $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
            $sql1 = "select count(*) as num ";
            $sql2 = "select sum(ny.paymentamount) as num ";
            $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
                . "inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber left JOIN fa_dahui_dingdan d on d.id = ny.dingdan_id "
                . "where ny.paymentdate = '{$paymentdate}' ";

            if (!empty($businesstype)) {
                $sql .= " and yw.businesstype = '" . $businesstype . "' ";
            }
            if (!empty($idnumber)) {
                $sql .= " and yw.idnumber = '" . $idnumber . "' ";
            }

            if (!empty($taobaoname)) {
                $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
            }

            if (!empty($newjoin_status)) {
                $sql .= " and ny.newjoin_status = '" . $newjoin_status . "' ";
            }
            if (!empty($payingcompany)) {
                $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
            }
            if (!empty($paymentnumber)) {
                $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
            }

            //不需要此条件，新客户，转出或退款都是新客户
//            $sql .= " and ny.refundornot <> '已退款' and ny.zc_status <> '已办理'";
//            $sql .= " and ny.refundornot <> '已退款' ";
            $sql .= " and ny.refundornot = '' ";

            //好好学习-- 上一个月没有同一个公司的年月记录
            $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));

            $sql .= " and (not exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '{$paymentdate_pre}' and payingcompany = ny.payingcompany and refundornot <> '已退款' and zc_status <> '已办理') or d.newkehu = '是'  )";

            $total = Db::query($sql1 . $sql);
            $money = Db::query($sql2 . $sql);
            if ($limit > 0) {
                $sql .= " limit " . $limit . " offset " . $offset;
            }

            $list = Db::query($sql0 . $sql);

            $result = array("total" => $total[0]['num'], "rows" => $list, "extend" => ['money' => sprintf("%.2f", $money[0]['num'])]);


            return json($result);
        }

        return $this->view->fetch();
    }

    /**
     * 转出客户
     * 上月有缴费记录，本月没有
     * 月初数据很多，每日应该递减
     */
    public function index5()
    {
        if ($this->request->isAjax()) {
            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);

            $offset = $this->request->get("offset", 0);
            $limit = $this->request->get("limit", 0);

            $idnumber = "";
            $paymentnumber = "";
            $businesstype = "";
            $paymentdate = "";
            $newjoin_status = "";
            $payingcompany = "";
            $zc_status = "";
            $earlytransfer = "";
            $taobaoname = "";
            if (isset($filter['idnumber'])) {
                $idnumber = $filter['idnumber'];
            }
            if (isset($filter['taobaoname'])) {
                $taobaoname = $filter['taobaoname'];
            }
            if (isset($filter['businesstype'])) {
                $businesstype = $filter['businesstype'];
            }
            if (isset($filter['paymentdate'])) {
                $paymentdate = $filter['paymentdate'];
            }

//            if (isset($filter['newjoin_status'])) {
//                $newjoin_status = $filter['newjoin_status'];
//            }
            if (isset($filter['payingcompany'])) {
                $payingcompany = $filter['payingcompany'];
            }
            if (isset($filter['paymentnumber'])) {
                $paymentnumber = $filter['paymentnumber'];
            }

            if (isset($filter['zc_status'])) {
                $zc_status = $filter['zc_status'];
            }
            if (isset($filter['earlytransfer'])) {
                $earlytransfer = $filter['earlytransfer'];
            }

            if (empty($paymentdate)) {
                $result = array("total" => 0, "rows" => array());
                return json($result);
            }

            $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));

            $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
            $sql1 = "select count(*) as num ";
            $sql2 = "select sum(ny.paymentamount) as num ";
            $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
                . " inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber"
                . " where ny.paymentdate = '" . $paymentdate_pre . "' ";

            if (!empty($businesstype)) {
                $sql .= " and yw.businesstype = '" . $businesstype . "' ";
            }
            if (!empty($idnumber)) {
                $sql .= " and yw.idnumber = '" . $idnumber . "' ";
            }

//            if (!empty($newjoin_status)) {
//                $sql .= " and ny.newjoin_status = '" . $newjoin_status . "' ";
//            }
            $sql .= " and ny.newjoin_status <> '办理失败' ";

            if (!empty($payingcompany)) {
                $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
            }
            if (!empty($paymentnumber)) {
                $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
            }

            if (!empty($earlytransfer)) {
                $sql .= " and ny.earlytransfer = '" . $earlytransfer . "' ";
            }

            if (!empty($taobaoname)) {
                $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
            }

            if (!empty($zc_status)) {
                $sql .= " and ny.zc_status = '" . $zc_status . "' ";  //是改上个月的记录，是否转出；因为这个月没有记录
            }

            $sql .= " and ny.refundornot  = ''  ";


//            $sql .= " and not exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '"
//                . $paymentdate . "' and payingcompany = ny.payingcompany and refundornot <> '已退款' and zc_status <> '已办理')";
            $sql .= " and (ny.zc_status  = '已办理' or not exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '"
                . $paymentdate . "' and payingcompany = ny.payingcompany and refundornot = '' ) )";


            $total = Db::query($sql1 . $sql);
            $money = Db::query($sql2 . $sql);
            if ($limit > 0) {
                $sql .= " limit " . $limit . " offset " . $offset;
            }
            $list = Db::query($sql0 . $sql);

            foreach ($list as &$row) {
                $row['newjoindate'] = "";
                $ny = \app\admin\model\dahui\Nianyue::where('businessnumber', $row['businessnumber'])
                    ->where('newjoin_status', '已办理')
                    ->order('id desc')
                    ->field('paymentdate')
                    ->limit(1)
                    ->find();
                if ($ny) {
                    $ny = $ny->toArray();
                    $row['newjoindate'] = $ny['paymentdate'];
                }

//                $this->importrow($row);
                //转出年月
                $row['paymentdate2'] = date("Y-m", strtotime("+1 months", strtotime($row['paymentdate'])));
            }

            $result = array("total" => $total[0]['num'], "rows" => $list, "extend" => ['money' => sprintf("%.2f", $money[0]['num'])]);

            return json($result);
        }

        return $this->view->fetch();
    }

    /**
     * 退款客户
     */
    public function index6()
    {
        if ($this->request->isAjax()) {
            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);

            $offset = $this->request->get("offset", 0);
            $limit = $this->request->get("limit", 0);

            $idnumber = "";
            $paymentnumber = "";
            $businesstype = "";
            $paymentdate = "";
            $newjoin_status = "";
            $payingcompany = "";
            $taobaoname = "";
            if (isset($filter['idnumber'])) {
                $idnumber = $filter['idnumber'];
            }
            if (isset($filter['taobaoname'])) {
                $taobaoname = $filter['taobaoname'];
            }
            if (isset($filter['businesstype'])) {
                $businesstype = $filter['businesstype'];
            }
            if (isset($filter['paymentdate'])) {
                $paymentdate = $filter['paymentdate'];
            }

            if (isset($filter['newjoin_status'])) {
                $newjoin_status = $filter['newjoin_status'];
            }
            if (isset($filter['payingcompany'])) {
                $payingcompany = $filter['payingcompany'];
            }
            if (isset($filter['paymentnumber'])) {
                $paymentnumber = $filter['paymentnumber'];
            }

            if (isset($filter['refundornot'])) {
                $refundornot = $filter['refundornot'];
            }

            if (empty($paymentdate)) {
                $result = array("total" => 0, "rows" => array());
                return json($result);
            }

            $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
            $sql1 = "select count(*) as num ";
            $sql2 = "select sum(ny.paymentamount) as num ";
            $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
                . " inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber"
                . " where ny.paymentdate = '" . $paymentdate . "' ";

            if (!empty($businesstype)) {
                $sql .= " and yw.businesstype = '" . $businesstype . "' ";
            }
            if (!empty($idnumber)) {
                $sql .= " and yw.idnumber = '" . $idnumber . "' ";
            }

            if (!empty($newjoin_status)) {
                $sql .= " and ny.newjoin_status = '" . $newjoin_status . "' ";
            }
            if (!empty($payingcompany)) {
                $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
            }
            if (!empty($paymentnumber)) {
                $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
            }

            if (!empty($taobaoname)) {
                $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
            }

            if (!empty($refundornot)) {
                $sql .= " and ny.refundornot = '" . $refundornot . "' ";
            } else {
                $sql .= " and (ny.refundornot = '已退款' or ny.refundornot = '待退款') ";
            }

//            $sql .= " and ny.zc_status <> '已办理'";

            $total = Db::query($sql1 . $sql);
            $money = Db::query($sql2 . $sql);
            if ($limit > 0) {
                $sql .= " limit " . $limit . " offset " . $offset;
            }
            $list = Db::query($sql0 . $sql);

            if ($limit == 0) {
                //导出
//                foreach ($list as &$row) {
//                    $this->importrow($row);
//                }
            }

//            $result = array("total" => $total[0]['num'], "rows" => $list);
            $result = array("total" => $total[0]['num'], "rows" => $list, "extend" => ['money' => sprintf("%.2f", $money[0]['num'])]);

            return json($result);
        }

        return $this->view->fetch();
    }

    /**
     * 问题客户
     * 上月在新入客户中办理失败的，到本月就转为问题客户
     */
    public function index7()
    {
        if ($this->request->isAjax()) {
            $filter = $this->request->get("filter", '');
            $filter = (array)json_decode($filter, true);

            $offset = $this->request->get("offset", 0);
            $limit = $this->request->get("limit", 0);

            $idnumber = "";
            $paymentnumber = "";
            $businesstype = "";
            $payingcompany = "";
            $taobaoname = "";
            $refundornot = "";

            if (isset($filter['idnumber'])) {
                $idnumber = $filter['idnumber'];
            }

            if (isset($filter['taobaoname'])) {
                $taobaoname = $filter['taobaoname'];
            }

            if (isset($filter['businesstype'])) {
                $businesstype = $filter['businesstype'];
            }

            if (isset($filter['payingcompany'])) {
                $payingcompany = $filter['payingcompany'];
            }
            if (isset($filter['paymentnumber'])) {
                $paymentnumber = $filter['paymentnumber'];
            }

            if (isset($filter['refundornot'])) {
                $refundornot = $filter['refundornot'];
            }

            if (isset($filter['paymentdate'])) {
                $paymentdate = $filter['paymentdate'];
            } else {
                $paymentdate = date("Y-m");//当前月
            }

            $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));

            $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
            $sql1 = "select count(*) as num ";
            $sql2 = "select sum(ny.paymentamount) as num ";
            $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
                . "inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber "
                . "where ny.paymentdate = '{$paymentdate}' ";

            if (!empty($businesstype)) {
                $sql .= " and yw.businesstype = '" . $businesstype . "' ";
            }
            if (!empty($idnumber)) {
                $sql .= " and yw.idnumber = '" . $idnumber . "' ";
            }

            if (!empty($taobaoname)) {
                $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
            }

            if (!empty($payingcompany)) {
                $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
            }
            if (!empty($paymentnumber)) {
                $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
            }

            if (!empty($refundornot)) {
                $sql .= " and ny.refundornot = '" . $refundornot . "' ";
            }

            $sql .= " and ( (ny.newjoin_status = '办理失败' and ny.refundornot = '') or ny.refundornot = '需退款') ";

            $total = Db::query($sql1 . $sql);
            $money = Db::query($sql2 . $sql);
            if ($limit > 0) {
                $sql .= " limit " . $limit . " offset " . $offset;
            }
            $list = Db::query($sql0 . $sql);

            if ($limit == 0) {
                //导出
                foreach ($list as &$row) {
                    $this->importrow($row);
                }
            }

            $result = array("total" => $total[0]['num'], "rows" => $list, "extend" => ['money' => sprintf("%.2f", $money[0]['num'])]);

            return json($result);
        }

        return $this->view->fetch();
    }

    /**
     * 联动搜索 -- 业务+公司
     *  select businesstype as name  from fa_dahui_gongshi where 1 group by businesstype
     */
    public function cxselect()
    {
        $type = $this->request->get('type', '');
        $businesstype = $this->request->get('businesstype', '');

        $list = null;
        if ($type == 'businesstype') {
            $gsmodel = new \app\admin\model\dahui\Gongshi;
            $list = $gsmodel
                ->group('businesstype')
                ->field('businesstype as value, businesstype as name')
                ->select();

//            $list[] = ['name' => '个税'];
//            $list[] = ['name' => '公积金'];
        } else if ($businesstype !== '') {
            $gsmodel = new \app\admin\model\dahui\Gongshi;
            $list = $gsmodel->where('businesstype', $businesstype)
                ->field('name as value, name')
                ->select();
        }

        $this->success('', null, $list);
    }

    /**
     * 是否退款 状态
     */
    //http://dahui.test/admin/dahui/dingdan/changetstatus
    public function changetstatus($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($this->tuikuan) {
            if ($row->refundornot == '') {
                $row->refundornot = '待退款';
            } else if ($row->refundornot == '待退款') {
                $row->refundornot = '已退款';
            } else if ($row->refundornot == '已退款') {
                $row->refundornot = '需退款';
            } else if ($row->refundornot == '需退款') {
                $row->refundornot = '';
            }
        } else if ($this->caozuo) {
            if ($row->refundornot == '') {
                $row->refundornot = '需退款';
            } else if ($row->refundornot == '需退款') {
                $row->refundornot = '';
            }
        } else if ($this->audit) {
            if ($row->refundornot == '') {
                $row->refundornot = '待退款';
            } else if ($row->refundornot == '待退款') {
                $row->refundornot = '需退款';
            } else if ($row->refundornot == '需退款') {
                $row->refundornot = '';
            }
        }

        $row = $this->setquanxianlog2($row);

        $row->save();
        $this->success("退款状态修改成功", null, ['refundornot' => $row->refundornot]);

    }

    /**
     * 是否退款 状态 --操作人员
     */
    //http://dahui.test/admin/dahui/dingdan/changetstatus2
    public function changetstatus2($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        $msg = '无修改权限';
        if ($row->refundornot == '') {
            $row->refundornot = '需退款';
            $msg = '退款状态修改成功';
        } else if ($row->refundornot == '需退款') {
            $row->refundornot = '';
            $msg = '退款状态修改成功';
        }

        $row = $this->setquanxianlog2($row);

        $row->save();
        $this->success($msg, null, ['refundornot' => $row->refundornot]);

    }

    //退款客户，操作
    public function changetstatus3($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($this->tuikuan) {
            if ($row->refundornot == '') {
                $row->refundornot = '待退款';
            } else if ($row->refundornot == '待退款') {
                $row->refundornot = '已退款';
            } else if ($row->refundornot == '已退款') {
                $row->refundornot = '';
            }
        } else {
            if ($row->refundornot == '') {
                $row->refundornot = '待退款';
            } else if ($row->refundornot == '待退款') {
                $row->refundornot = '';
            }
        }

        $row = $this->setquanxianlog2($row);

        $row->save();
        $this->success("退款状态修改成功", null, ['refundornot' => $row->refundornot]);

    }

    //退款员
    //从待退款状态 点击后 变更为 已退款（另外有时候误操作情况下 他可以把已退款变更为  --状态 让其他角色重新操作）
    public function changetstatus4($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($row->refundornot == '待退款') {
            $row->refundornot = '已退款';
        } else if ($row->refundornot == '已退款') {
            $row->refundornot = '';
        } else if ($row->refundornot == '') {
            $row->refundornot = '已退款';
        }
//        $row = $this->setquanxianlog2($row);

        $row->save();
        $this->success("退款状态修改成功", null, ['refundornot' => $row->refundornot]);

    }

    /**
     * 新入状态++
     */
    //http://dahui.test/admin/dahui/dingdan/changexrstatus
    public function changexrstatus($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($row->newjoin_status == '办理失败') {
            $row->newjoin_status = '已办理';
/*
            $yw = \app\admin\model\dahui\Yewu::where('businessnumber', $row['businessnumber'])
                ->field('businesstype, idnumber')
                ->find();
            if ($yw) {
                $kehu = \app\admin\model\dahui\Kehu::where('idnumber', $yw->idnumber)
                    ->field('name,mobile')
                    ->find();

                if ($kehu) {
//                    $this->sendmsg($kehu->mobile, $yw->businesstype, $kehu->name);
                }
            }
*/

        } else if ($row->newjoin_status == '未办理') {
            $row->newjoin_status = '办理失败';
        } else if ($row->newjoin_status == '已办理') {
            $row->newjoin_status = '未办理';
        }

        $row = $this->setquanxianlog2($row);

        $row->save();
        $this->success("新入状态修改成功", null, ['changexrstatus' => $row->newjoin_status]);

    }

    //批量新入状态
    public function plxrstatus1($ids = null)
    {
        $list = \app\admin\model\dahui\Nianyue::where('id', 'in', $ids)->select();

        foreach ($list as $row) {
            $row->newjoin_status = '已办理';
            $row = $this->setquanxianlog2($row);
            $row->save();

        }
        $this->success();
    }

    public function plxrstatus2($ids = null)
    {
        $list = \app\admin\model\dahui\Nianyue::where('id', 'in', $ids)->select();

        foreach ($list as $row) {
            $row->newjoin_status = '办理失败';
            $row = $this->setquanxianlog2($row);
            $row->save();

        }
        $this->success();
    }

    /**
     * 转出状态++
     */
    //http://dahui.test/admin/dahui/dingdan/changezcstatus
    public function changezcstatus($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($row->zc_status == '未办理') {
            $row->zc_status = '办理失败';

        } else if ($row->zc_status == '已办理') {
            $row->zc_status = '未办理';
        } else if ($row->zc_status == '办理失败') {
            $row->zc_status = '已办理';
/*
            $yw = \app\admin\model\dahui\Yewu::where('businessnumber', $row['businessnumber'])
                ->field('businesstype, idnumber')
                ->find();

            if ($yw) {
                $kehu = \app\admin\model\dahui\Kehu::where('idnumber', $yw->idnumber)
                    ->field('name,mobile')
                    ->find();

//                if ($kehu) {
//                    $this->sendmsg2($kehu->mobile, $yw->businesstype, $kehu->name);
//                }
            }
*/
        }

        $row = $this->setquanxianlog2($row);

        $row->save();
        $this->success("转出状态修改成功", null, ['zc_status' => $row->zc_status]);

    }

    //批量未办理状态
    public function plzcstatus0($ids = null)
    {
        $list = \app\admin\model\dahui\Nianyue::where('id', 'in', $ids)->select();

        foreach ($list as $row) {
            $row->zc_status = '未办理';
            $row = $this->setquanxianlog2($row);
            $row->save();

        }
        $this->success();
    }

    //批量转出状态
    public function plzcstatus1($ids = null)
    {
        $list = \app\admin\model\dahui\Nianyue::where('id', 'in', $ids)->select();

        foreach ($list as $row) {
            $row->zc_status = '已办理';
            $row = $this->setquanxianlog2($row);
            $row->save();

        }
        $this->success();
    }

    public function plzcstatus2($ids = null)
    {
        $list = \app\admin\model\dahui\Nianyue::where('id', 'in', $ids)->select();

        foreach ($list as $row) {
            $row->zc_status = '办理失败';
            $row = $this->setquanxianlog2($row);
            $row->save();

        }
        $this->success();
    }

    /**
     * 提前转出状态++
     */
    //http://dahui.test/admin/dahui/dingdan/changetqzcstatus
    public function changetqzcstatus($ids = null)
    {
        $row = \app\admin\model\dahui\Nianyue::get($ids);
        if (!$row) {
            $this->error(__('No Results were found'));
        }

        if ($row->earlytransfer == '是') {
            $row->earlytransfer = '否';
        } else {
            $row->earlytransfer = '是';
        }

        $row = $this->setquanxianlog2($row);

        $row->save();
        $this->success("提前转出状态修改成功", null, ['earlytransfer' => $row->earlytransfer]);

    }

    //http://dahui.test/admin/dahui/dingdan/xgjs0?yearpayment=2021-06
    //可以，但是很慢
    public function xgjs0()
    {
        $thismoth = $this->request->get('yearpayment', '');
        if (empty($thismoth)) {
            $this->error("无效的年月！");
        }

        $sql = "update fa_dahui_nianyue set paymentbase =
case when
 (select minimumcardinality from fa_dahui_gongshi
     where businesstype= (select businesstype from fa_dahui_yewu where fa_dahui_nianyue.businessnumber=fa_dahui_yewu.businessnumber)
     and name=payingcompany)
 >paymentbase
then
(select minimumcardinality from fa_dahui_gongshi
	where businesstype=(select businesstype from fa_dahui_yewu where fa_dahui_nianyue.businessnumber=fa_dahui_yewu.businessnumber)
	and name=payingcompany)
else
	paymentbase
end
where paymentdate>='{$thismoth}'";

        Db::execute($sql);

        $sql = "update fa_dahui_nianyue set paymentamount =
paymentbase * (select proportion from fa_dahui_gongshi where businesstype=(select businesstype from fa_dahui_yewu where fa_dahui_nianyue.businessnumber=fa_dahui_yewu.businessnumber) and name=payingcompany)
where paymentdate>='{$thismoth}'";

        Db::execute($sql);

        $this->success("批量修改基数");
    }

    //http://dahui.test/admin/dahui/dingdan/xgjs?yearpayment=2021-06
    //速度还行，可以忍受
    public function xgjs()
    {
        $thismoth = $this->request->get('yearpayment', '');
        if (empty($thismoth)) {
            $this->error("无效的年月！");
        }

        $gsmodel = new \app\admin\model\dahui\Gongshi();
        $gslist = $gsmodel->field('businesstype,name,minimumcardinality,proportion')->select();
        $gslist = collection($gslist)->toArray();
        foreach ($gslist as &$row) {
            $row['miniamount'] = round($row['minimumcardinality'] * $row['proportion'], 1);
        }

        $nymodel = new \app\admin\model\dahui\Nianyue();
        $nylist = $nymodel
            ->field('id, payingcompany, paymentbase, paymentamount')//field需在with前，否则field就无效了
            ->with(['yewu'])
            ->where('paymentdate', '>=', $thismoth)
            ->select();

        foreach ($nylist as &$ny) {
            $businesstype = $ny->yewu->businesstype;
            $minimumcardinality = 0;
            $miniamount = 0;
            foreach ($gslist as $row) {
                if ($row['name'] == $ny->payingcompany && $row['businesstype'] == $businesstype) {
                    $minimumcardinality = $row['minimumcardinality'];
                    $miniamount = $row['miniamount'];
                    break;
                }
            }
            if ($ny->paymentbase < $minimumcardinality) {
                $ny->paymentbase = $minimumcardinality;
                $ny->paymentamount = $miniamount;
                $ny->save();
            }
        }

        $this->success("批量修改基数成功");
    }

    //http://dahui.test/admin/dahui/dingdan/import0
    //index0 订单管理
    public function import0()
    {
        $this->relationSearch = true;
        list($where, $sort, $order, $offset, $limit) = $this->buildparams();
        $count = $this->model
            ->with(['kehu'])
            ->where($where)
            ->order('orderdate desc')
            ->count();

        $fileName = "订单";

        set_time_limit(0);
        ini_set('memory_limit', '-1');

        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');

        //一次取5000条数据
        $step = ceil($count/5000);

        $title = array('审核状态', '新客户', '预处理', '录入日期', '业务类型', '淘宝名称',
            '身份证号', '客户姓名', '客户电话', '缴纳号码', '缴纳公司', '缴纳基数', '缴纳金额',
            '缴纳年月', '付代理费', '订单备注', '录入员', '审核员', '操作员');

        //将标题写到标准输出中
        fputcsv($fp, $title);

        for($s = 0; $s < $step; $s++) {
            $page_count = $s;
            $page_size = 5000;
            $offset = $page_count * $page_size;
            $list = $this->model
                ->with(['kehu'])
                ->where($where)
                ->order('orderdate desc')
                ->limit($offset, $page_size)
                ->select();
//            $data = collection($list)->toArray();
//            continue;

            foreach ($list as $row) {
                $v = array(
                    $row['auditstatus'],
                    $row['newkehu'],
                    $row['preprocessing'],
                    "\t" .$row['orderdate'],
                    $row['businesstype'],
                    $row['taobaoname'],
                    "\t" .$row['idnumber'],
                    $row['kehu']['name'],
                    "\t" .$row['kehu']['mobile'],
                    "\t" .$row['paymentnumber'],
                    $row['payingcompany'],
                    $row['paymentbase'],
                    $row['paymentamount'],
                    "\t" .$row['paymentdate'],
                    $row['agencyfees'],
                    $row['ordercomment'],
                    $row['luruman'],
                    $row['auditman'],
                    $row['opman']
                );

                fputcsv($fp, $v);
            }
            //每5000条数据就刷新缓冲区
            ob_flush();
            flush();
        }
        exit();
    }

    //http://dahui.test/admin/dahui/dingdan/import1
    //index1 缴纳明细
    public function import1()
    {
        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);
        $idnumber = "";
        $businesstype = "";
        if (isset($filter['idnumber'])) {
            $idnumber = $filter['idnumber'];
        }
        if (isset($filter['businesstype'])) {
            $businesstype = $filter['businesstype'];
        }

        if (empty($idnumber)) {
            return;
        }

        $list = [];
        if (strlen($businesstype) > 1) {
            $ywlist = \app\admin\model\dahui\Yewu::where('idnumber', $idnumber)
                ->where('businesstype', $businesstype)
                ->field('id,businessnumber,businesstype,idnumber,paymentnumber,agencyfees')
                ->select();
        } else {
            $ywlist = \app\admin\model\dahui\Yewu::where('idnumber', $idnumber)
                ->field('id,businessnumber,businesstype,idnumber,paymentnumber,agencyfees')
                ->select();
        }

        $kehu = \app\admin\model\dahui\Kehu::where('idnumber', $idnumber)
            ->field('name,mobile,note')
            ->find()->toArray();

        foreach ($ywlist as &$yw) {
            $nlist = collection(\app\admin\model\dahui\Nianyue::where('businessnumber', $yw['businessnumber'])
                ->select())->toArray();

            foreach ($nlist as $nianyue) {
                $nianyue['kehu'] = $kehu;
                $nianyue['idnumber'] = $idnumber;
                $nianyue['businesstype'] = $yw['businesstype'];
                $nianyue['paymentnumber'] = $yw['paymentnumber'];
                $list[] = $nianyue;
            }
        }

        $fileName = "缴纳明细_" . $idnumber;

        set_time_limit(0);
        ini_set('memory_limit', '-1');

        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');
        $fp = fopen('php://output', 'a');
        $title = array('业务类型', '缴纳年月', '淘宝名称', '客户姓名', '客户电话', '缴纳号码', '缴纳公司', '缴纳基数', '缴纳金额', '是否退款', '录入员', '审核员', '操作员');
        fputcsv($fp, $title);
        foreach ($list as $row) {
            $v = array(
                $row['businesstype'],
                "\t" . $row['paymentdate'],
                $row['taobaoname'],
                $row['kehu']['name'],
                "\t" . $row['kehu']['mobile'],
                "\t" . $row['paymentnumber'],
                $row['payingcompany'],
                "\t" . $row['paymentbase'],
                "\t" . $row['paymentamount'],
                "\t" . $row['refundornot'],
                $row['luruman'],
                $row['auditman'],
                $row['opman']
            );

            fputcsv($fp, $v);
        }

    }

    //index2 导出续费提醒
    public function import2()
    {
        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);

        $idnumber = "";
        $paymentnumber = "";
        $businesstype = "";
        $paymentdate = "";
        $payingcompany = "";
        $taobaoname = "";
        if (isset($filter['idnumber'])) {
            $idnumber = $filter['idnumber'];
        }
        if (isset($filter['taobaoname'])) {
            $taobaoname = $filter['taobaoname'];
        }
        if (isset($filter['businesstype'])) {
            $businesstype = $filter['businesstype'];
        }
        if (isset($filter['paymentdate'])) {
            $paymentdate = $filter['paymentdate'];
        }

        if (isset($filter['payingcompany'])) {
            $payingcompany = $filter['payingcompany'];
        }
        if (isset($filter['paymentnumber'])) {
            $paymentnumber = $filter['paymentnumber'];
        }

        if (empty($paymentdate)) {
            $paymentdate = date("Y-m");
        }

        $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));

        $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
//        $sql0 .= ", (select count(*) from fa_dahui_dingdan d where d.businesstype=yw.businesstype and d.idnumber=yw.idnumber and d.auditstatus='未审核'
//and d.paymentdate= '" . $paymentdate_pre . "') n0,";//确定
//        $sql0 .= " (select count(1) from fa_dahui_dingdan where businesstype=yw.businesstype and idnumber=yw.idnumber and auditstatus='未审核') n1 ";//可能录入

        $sql1 = "select count(*) as num ";
        $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
            . " inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber"
            . " where ny.paymentdate = '" . $paymentdate_pre . "' ";

        if (!empty($businesstype)) {
            $sql .= " and yw.businesstype = '" . $businesstype . "' ";
        }
        if (!empty($idnumber)) {
            $sql .= " and yw.idnumber = '" . $idnumber . "' ";
        }

        if (!empty($taobaoname)) {
            $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
        }

        if (!empty($payingcompany)) {
            $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
        }
        if (!empty($paymentnumber)) {
            $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
        }

        $sql .= " and ny.refundornot <> '已退款' and ny.zc_status <> '已办理' and ny.earlytransfer <> '是' ";

//上个月交了，这个月没有交
        $sql .= " and not exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '"
            . $paymentdate . "' and refundornot <> '已退款') ";//order by yw.idnumber asc

        //且没有订单
        $sql .= " and not exists(select id from fa_dahui_dingdan where idnumber=yw.idnumber and businesstype=yw.businesstype and paymentdate like '%"
            . $paymentdate . "%' and auditstatus='未审核' ) ";

        $total = Db::query($sql1 . $sql);
        $count = $total[0]['num'];
        $sqlXX = $sql0 . $sql;

        $fileName = "续费提醒[" . $paymentdate . "]";

        set_time_limit(0);
        ini_set('memory_limit', '-1');

        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');

        //一次取5000条数据
        $step = ceil($count/5000);

        $title = array('业务类型	', '提醒年月', '淘宝名称', '身份证号', '客户姓名', '客户电话', '缴纳号码', '缴纳公司', '缴纳基数', '缴纳金额', '付代理费', '已录入', '已转出');

        //将标题写到标准输出中
        fputcsv($fp, $title);

        for($s = 0; $s < $step; $s++) {
            $page_count = $s;
            $page_size = 5000;
            $offset = $page_count * $page_size;
            $data = Db::query($sqlXX." limit 5000 offset ".$offset);
            foreach ($data as &$row) {
//                $row['ylr'] = "否";//已录入
//                if (intval($row['n0']) > 0) {
//                    $row['ylr'] = "是";
//                }
//                if (intval($row['n1']) > 0) {
//                    $row['ylr'] = "可能";
//                }
//
//                //是—已转出、否—未转出、可能—已登记但未转出
//                $row['yzc'] = "否";
//                if ($row['zc_status'] == '已办理') {
//                    $row['yzc'] = "是";
//                } else {
//                    if ($row['earlytransfer'] == '是') {
//                        $row['yzc'] = "可能";
//                    }
//                }

                //提醒年月
                $row['paymentdate2'] = date("Y-m", strtotime("+1 months", strtotime($row['paymentdate'])));

                $v = array(
                    $row['businesstype'],
                    "\t" .$row['paymentdate2'],
                    $row['taobaoname'],
                    "\t" .$row['idnumber'],
                    $row['kehuname'],
                    $row['kehumobile'],
                    "\t" .$row['paymentnumber'],
                    $row['payingcompany'],
                    $row['paymentbase'],
                    $row['paymentamount'],
                    $row['agencyfees'],
                );

/*
                $v = array(
                    iconv('UTF-8', $chartset, $row['businesstype']),
                    iconv('UTF-8', $chartset, "\t" .$row['paymentdate2']),
                    iconv('UTF-8', $chartset, $row['taobaoname']),
                    iconv('UTF-8', $chartset, "\t" .$row['idnumber']),
                    iconv('UTF-8', $chartset, $row['kehuname']),
                    iconv('UTF-8', $chartset, $row['kehumobile']),
                    iconv('UTF-8', $chartset, "\t" .$row['paymentnumber']),
                    iconv('UTF-8', $chartset, $row['payingcompany']),
                    iconv('UTF-8', $chartset, $row['paymentbase']),
                    iconv('UTF-8', $chartset, $row['paymentamount']),
                    iconv('UTF-8', $chartset, $row['agencyfees']),
//                    $row['ylr'],
//                    $row['yzc']
                );
*/
                fputcsv($fp, $v);
            }
            //每5000条数据就刷新缓冲区
            ob_flush();
            flush();
        }
        exit();
    }

    //http://dahui.test/admin/dahui/dingdan/import3
    //index3 导出续费客户
    public function import3()
    {
        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);

        $idnumber = "";
        $paymentnumber = "";
        $businesstype = "";
        $paymentdate = "";
        $payingcompany = "";
        $taobaoname = "";
        if (isset($filter['idnumber'])) {
            $idnumber = $filter['idnumber'];
        }
        if (isset($filter['taobaoname'])) {
            $taobaoname = $filter['taobaoname'];
        }
        if (isset($filter['businesstype'])) {
            $businesstype = $filter['businesstype'];
        }
        if (isset($filter['paymentdate'])) {
            $paymentdate = $filter['paymentdate'];
        }

        if (isset($filter['payingcompany'])) {
            $payingcompany = $filter['payingcompany'];
        }
        if (isset($filter['paymentnumber'])) {
            $paymentnumber = $filter['paymentnumber'];
        }

        if (!$paymentdate) {
            $paymentdate = date("Y-m");
        }

        //读取数据
        $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));
        $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
        $sql1 = "select count(*) as num ";
        $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
            . " inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber"
            . " left JOIN fa_dahui_dingdan d on d.id = ny.dingdan_id "
            . " where ny.paymentdate = '" . $paymentdate . "' ";

        if (!empty($businesstype)) {
            $sql .= " and yw.businesstype = '" . $businesstype . "' ";
        }
        if (!empty($idnumber)) {
            $sql .= " and yw.idnumber = '" . $idnumber . "' ";
        }
        if (!empty($taobaoname)) {
            $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
        }
        if (!empty($payingcompany)) {
            $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
        }
        if (!empty($paymentnumber)) {
            $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
        }

//        $sql .= " and ny.refundornot <> '已退款' and ny.zc_status <> '已办理'";
//        $sql .= " and exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '"
//            . $paymentdate_pre . "' and payingcompany = ny.payingcompany and refundornot <> '已退款' and zc_status <> '已办理') ";
        $sql .= " and ny.refundornot = '' ";
        $sql .= " and exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '"
            . $paymentdate_pre . "' and payingcompany = ny.payingcompany and refundornot <> '已退款' and zc_status <> '已办理' ) ";
        $sql .= " and ( ny.dingdan_id = 0 or d.newkehu <> '是' )  ";

        $sqlXX = $sql0 . $sql;
        $total = Db::query($sql1 . $sql);
        $count = $total[0]['num'];

        $fileName = "续费客户[" . $paymentdate . "]";

        set_time_limit(0);
        ini_set('memory_limit', '-1');

        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');

        //一次取5000条数据
        $step = ceil($count/5000);

        $title = array(
            '业务类型', '续费年月', '淘宝名称', '身份证号', '客户姓名', '客户电话', '缴纳号码', '缴纳公司', '缴纳基数', '缴纳金额', '付代理费', '录入员', '审核员', '操作员');

        //将标题写到标准输出中
        fputcsv($fp, $title);

        for($s = 0; $s < $step; $s++) {
            $page_count = $s;
            $page_size = 5000;
            $offset = $page_count * $page_size;
            $data = Db::query($sqlXX." limit 5000 offset ".$offset);
            foreach ($data as &$row) {
//                iconv('UTF-8', 'GBK', $row['businesstype']),
                $v = array(
                    $row['businesstype'],
                    "\t" .$row['paymentdate'],
                    $row['taobaoname'],
                    "\t" .$row['idnumber'],
                    $row['kehuname'],
                    $row['kehumobile'],
                    "\t" .$row['paymentnumber'],
                    $row['payingcompany'],
                    $row['paymentbase'],
                    $row['paymentamount'],
                    $row['agencyfees'],
                    $row['luruman'],
                    $row['auditman'],
                    $row['opman']
                );

                fputcsv($fp, $v);
            }
            //每5000条数据就刷新缓冲区
            ob_flush();
            flush();
        }
        exit();
    }

    //index4 导出新入客户
    public function import4()
    {
        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);

        $idnumber = "";
        $paymentnumber = "";
        $businesstype = "";
        $paymentdate = "";
        $newjoin_status = "";
        $payingcompany = "";
        $taobaoname = "";
        if (isset($filter['idnumber'])) {
            $idnumber = $filter['idnumber'];
        }

        if (isset($filter['taobaoname'])) {
            $taobaoname = $filter['taobaoname'];
        }

        if (isset($filter['businesstype'])) {
            $businesstype = $filter['businesstype'];
        }
        if (isset($filter['paymentdate'])) {
            $paymentdate = $filter['paymentdate'];
        }

        if (isset($filter['newjoin_status'])) {
            $newjoin_status = $filter['newjoin_status'];
        }
        if (isset($filter['payingcompany'])) {
            $payingcompany = $filter['payingcompany'];
        }
        if (isset($filter['paymentnumber'])) {
            $paymentnumber = $filter['paymentnumber'];
        }

        if (empty($paymentdate)) {
            $paymentdate = date("Y-m");
        }

        $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
        $sql1 = "select count(*) as num ";

        $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
            . "inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber left JOIN fa_dahui_dingdan d on d.id = ny.dingdan_id "
            . "where ny.paymentdate = '{$paymentdate}' ";

        if (!empty($businesstype)) {
            $sql .= " and yw.businesstype = '" . $businesstype . "' ";
        }
        if (!empty($idnumber)) {
            $sql .= " and yw.idnumber = '" . $idnumber . "' ";
        }

        if (!empty($taobaoname)) {
            $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
        }

        if (!empty($newjoin_status)) {
            $sql .= " and ny.newjoin_status = '" . $newjoin_status . "' ";
        }
        if (!empty($payingcompany)) {
            $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
        }
        if (!empty($paymentnumber)) {
            $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
        }

//        $sql .= " and ny.refundornot <> '已退款' and ny.zc_status <> '已办理'";
//        $sql .= " and ny.refundornot <> '已退款' ";
        $sql .= " and ny.refundornot = '' ";

        //好好学习-- 上一个月没有同一个公司的年月记录
        $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));

        $sql .= " and (not exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '{$paymentdate_pre}' and payingcompany = ny.payingcompany and refundornot <> '已退款' and zc_status <> '已办理') or d.newkehu = '是' )";
        $total = Db::query($sql1 . $sql);

        $sqlXX = $sql0 . $sql;
        $count = $total[0]['num'];

        $fileName = "新入客户[" . $paymentdate . "]";

        set_time_limit(0);
        ini_set('memory_limit', '-1');

        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');

        //一次取5000条数据
        $step = ceil($count/5000);

        $title = array('业务类型','新入年月', '淘宝名称', '身份证号', '客户姓名', '客户电话', '缴纳号码', '缴纳公司', '缴纳基数', '缴纳金额', '付代理费', '新入备注', '新入状态', '录入员', '审核员', '操作员');

        //将标题写到标准输出中
        fputcsv($fp, $title);

        for($s = 0; $s < $step; $s++) {
            $page_count = $s;
            $page_size = 5000;
            $offset = $page_count * $page_size;
            $data = Db::query($sqlXX." limit 5000 offset ".$offset);
            foreach ($data as &$row) {
                $v = array(
                    $row['businesstype'],
                    "\t" .$row['paymentdate'],
                    $row['taobaoname'],
                    "\t" .$row['idnumber'],
                    $row['kehuname'],
                    $row['kehumobile'],
                    "\t" .$row['paymentnumber'],
                    $row['payingcompany'],
                    $row['paymentbase'],
                    $row['paymentamount'],
                    $row['agencyfees'],
                    $row['newjoin_note'],
                    $row['newjoin_status'],
                    $row['luruman'],
                    $row['auditman'],
                    $row['opman']
                );

                fputcsv($fp, $v);
            }
            //每5000条数据就刷新缓冲区
            ob_flush();
            flush();
        }
        exit();
    }

    //index5 导出转出客户
    public function import5()
    {
        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);

        $idnumber = "";
        $paymentnumber = "";
        $businesstype = "";
        $paymentdate = "";
        $newjoin_status = "";
        $payingcompany = "";
        $zc_status = "";
        $earlytransfer = "";
        $taobaoname = "";
        if (isset($filter['idnumber'])) {
            $idnumber = $filter['idnumber'];
        }
        if (isset($filter['taobaoname'])) {
            $taobaoname = $filter['taobaoname'];
        }
        if (isset($filter['businesstype'])) {
            $businesstype = $filter['businesstype'];
        }
        if (isset($filter['paymentdate'])) {
            $paymentdate = $filter['paymentdate'];
        }

//        if (isset($filter['newjoin_status'])) {
//            $newjoin_status = $filter['newjoin_status'];
//        }

        if (isset($filter['payingcompany'])) {
            $payingcompany = $filter['payingcompany'];
        }
        if (isset($filter['paymentnumber'])) {
            $paymentnumber = $filter['paymentnumber'];
        }

        if (isset($filter['zc_status'])) {
            $zc_status = $filter['zc_status'];
        }
        if (isset($filter['earlytransfer'])) {
            $earlytransfer = $filter['earlytransfer'];
        }

        if (empty($paymentdate)) {
            $paymentdate = date("Y-m");
        }

        $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));

        $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
        $sql1 = "select count(*) as num ";

        $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
            . " inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber"
            . " where ny.paymentdate = '" . $paymentdate_pre . "' ";

        if (!empty($businesstype)) {
            $sql .= " and yw.businesstype = '" . $businesstype . "' ";
        }
        if (!empty($idnumber)) {
            $sql .= " and yw.idnumber = '" . $idnumber . "' ";
        }

//        if (!empty($newjoin_status)) {
//            $sql .= " and ny.newjoin_status = '" . $newjoin_status . "' ";
//        }
        $sql .= " and ny.newjoin_status <> '办理失败' ";

        if (!empty($payingcompany)) {
            $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
        }
        if (!empty($paymentnumber)) {
            $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
        }

        if (!empty($earlytransfer)) {
            $sql .= " and ny.earlytransfer = '" . $earlytransfer . "' ";
        }

        if (!empty($taobaoname)) {
            $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
        }

        if (!empty($zc_status)) {
            $sql .= " and ny.zc_status = '" . $zc_status . "' ";
        }

        $sql .= " and ny.refundornot = '' ";

//        $sql .= " and not exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '"
//            . $paymentdate . "' and payingcompany = ny.payingcompany and refundornot <> '已退款' and zc_status <> '已办理')";

        //9月的转出客户，8月有，9月没有缴费记录； 9月有已转出的缴费记录也不是9月的转出客户--已否定，上月已转出则必
        $sql .= "  and (ny.zc_status  = '已办理' or  not exists(select businessnumber from fa_dahui_nianyue where businessnumber=ny.businessnumber and paymentdate = '"
            . $paymentdate . "' and payingcompany = ny.payingcompany and refundornot = '' ) )";

        $total = Db::query($sql1 . $sql);

        $sqlXX = $sql0 . $sql;
        $count = $total[0]['num'];

        $fileName = "转出客户[" . $paymentdate . "]";

        set_time_limit(0);
        ini_set('memory_limit', '-1');

        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');

        //一次取5000条数据
        $step = ceil($count/5000);

        $title = array('业务类型', '转出年月', '淘宝名称', '身份证号', '客户姓名', '客户电话', '缴纳号码', '缴纳公司', '缴纳基数', '缴纳金额', '提前转出', '转出备注', '新入年月', '转出状态', '录入员', '审核员', '操作员');

        //将标题写到标准输出中
        fputcsv($fp, $title);

        for($s = 0; $s < $step; $s++) {
            $page_count = $s;
            $page_size = 5000;
            $offset = $page_count * $page_size;
            $data = Db::query($sqlXX." limit 5000 offset ".$offset);
            foreach ($data as &$row) {
                $row['newjoindate'] = "";
                $ny = \app\admin\model\dahui\Nianyue::where('businessnumber', $row['businessnumber'])
                    ->where('newjoin_status', '已办理')
                    ->order('id desc')
                    ->field('paymentdate')
                    ->limit(1)
                    ->find();
                if ($ny) {
                    $ny = $ny->toArray();
                    $row['newjoindate'] = $ny['paymentdate'];
                }

                $row['paymentdate2'] = date("Y-m", strtotime("+1 months", strtotime($row['paymentdate'])));

                $v = array(
                    $row['businesstype'],
                    "\t" .$row['paymentdate2'],
                    $row['taobaoname'],
                    "\t" .$row['idnumber'],
                    $row['kehuname'],
                    $row['kehumobile'],
                    "\t" .$row['paymentnumber'],
                    $row['payingcompany'],
                    $row['paymentbase'],
                    $row['paymentamount'],
                    $row['earlytransfer'],
                    $row['zc_note'],
                    "\t" .$row['newjoindate'],
                    $row['zc_status'],
                    $row['luruman'],
                    $row['auditman'],
                    $row['opman']
                );

                fputcsv($fp, $v);
            }
            //每5000条数据就刷新缓冲区
            ob_flush();
            flush();
        }
        exit();
    }

    //index6 导出退款客户
    public function import6()
    {
        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);

        $idnumber = "";
        $paymentnumber = "";
        $businesstype = "";
        $paymentdate = "";
        $newjoin_status = "";
        $payingcompany = "";
        $taobaoname = "";
        if (isset($filter['idnumber'])) {
            $idnumber = $filter['idnumber'];
        }
        if (isset($filter['taobaoname'])) {
            $taobaoname = $filter['taobaoname'];
        }
        if (isset($filter['businesstype'])) {
            $businesstype = $filter['businesstype'];
        }
        if (isset($filter['paymentdate'])) {
            $paymentdate = $filter['paymentdate'];
        }

        if (isset($filter['newjoin_status'])) {
            $newjoin_status = $filter['newjoin_status'];
        }
        if (isset($filter['payingcompany'])) {
            $payingcompany = $filter['payingcompany'];
        }
        if (isset($filter['paymentnumber'])) {
            $paymentnumber = $filter['paymentnumber'];
        }

        if (isset($filter['refundornot'])) {
            $refundornot = $filter['refundornot'];
        }

        if (empty($paymentdate)) {
            $paymentdate = date("Y-m");
        }

        $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
        $sql1 = "select count(*) as num ";

        $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
            . " inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber"
            . " where ny.paymentdate = '" . $paymentdate . "' ";

        if (!empty($businesstype)) {
            $sql .= " and yw.businesstype = '" . $businesstype . "' ";
        }
        if (!empty($idnumber)) {
            $sql .= " and yw.idnumber = '" . $idnumber . "' ";
        }

        if (!empty($newjoin_status)) {
            $sql .= " and ny.newjoin_status = '" . $newjoin_status . "' ";
        }
        if (!empty($payingcompany)) {
            $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
        }
        if (!empty($paymentnumber)) {
            $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
        }

        if (!empty($taobaoname)) {
            $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
        }

        if (!empty($refundornot)) {
            $sql .= " and ny.refundornot = '" . $refundornot . "' ";
        } else {
            $sql .= " and (ny.refundornot = '已退款' or ny.refundornot = '待退款') ";
        }

//        $sql .= " and ny.zc_status <> '已办理'";

        $total = Db::query($sql1 . $sql);

        $sqlXX = $sql0 . $sql;
        $count = $total[0]['num'];

        $fileName = "退款客户[" . $paymentdate . "]";

        set_time_limit(0);
        ini_set('memory_limit', '-1');

        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');

        //一次取5000条数据
        $step = ceil($count/5000);

        $title = array('业务类型', '缴纳年月', '淘宝名称', '身份证号', '客户姓名', '客户电话', '缴纳号码', '缴纳公司', '缴纳基数', '缴纳金额', '退款状态', '录入员', '审核员', '操作员');

        //将标题写到标准输出中
        fputcsv($fp, $title);

        for($s = 0; $s < $step; $s++) {
            $page_count = $s;
            $page_size = 5000;
            $offset = $page_count * $page_size;
            $data = Db::query($sqlXX." limit 5000 offset ".$offset);
            foreach ($data as &$row) {

                $v = array(
                    $row['businesstype'],
                    "\t" .$row['paymentdate'],
                    $row['taobaoname'],
                    "\t" .$row['idnumber'],
                    $row['kehuname'],
                    $row['kehumobile'],
                    "\t" .$row['paymentnumber'],
                    $row['payingcompany'],
                    $row['paymentbase'],
                    $row['paymentamount'],
                    $row['refundornot'],
                    $row['luruman'],
                    $row['auditman'],
                    $row['opman']
                );

                fputcsv($fp, $v);
            }
            //每5000条数据就刷新缓冲区
            ob_flush();
            flush();
        }
        exit();
    }

    //index7 导出问题客户
    public function import7()
    {
        $filter = $this->request->get("filter", '');
        $filter = (array)json_decode($filter, true);

        $idnumber = "";
        $paymentnumber = "";
        $businesstype = "";
        $payingcompany = "";
        $taobaoname = "";
        $refundornot = "";

        if (isset($filter['idnumber'])) {
            $idnumber = $filter['idnumber'];
        }

        if (isset($filter['taobaoname'])) {
            $taobaoname = $filter['taobaoname'];
        }

        if (isset($filter['businesstype'])) {
            $businesstype = $filter['businesstype'];
        }

        if (isset($filter['payingcompany'])) {
            $payingcompany = $filter['payingcompany'];
        }
        if (isset($filter['paymentnumber'])) {
            $paymentnumber = $filter['paymentnumber'];
        }

        if (isset($filter['refundornot'])) {
            $refundornot = $filter['refundornot'];
        }

        if (isset($filter['paymentdate'])) {
            $paymentdate = $filter['paymentdate'];
        } else {
            $paymentdate = date("Y-m");//当前月
        }

        $newjoin_status = '办理失败';
        $paymentdate_pre = date("Y-m", strtotime("-1 months", strtotime($paymentdate)));

        $sql0 = "select ny.*, yw.businesstype as businesstype, yw.idnumber as idnumber, yw.paymentnumber as paymentnumber, yw.agencyfees as agencyfees, k.name as kehuname, k.mobile as kehumobile ";
        $sql1 = "select count(*) as num ";
        $sql = "from fa_dahui_nianyue ny inner JOIN fa_dahui_yewu yw on ny.businessnumber = yw.businessnumber "
            . "inner JOIN fa_dahui_kehu k on k.idnumber = yw.idnumber "
            . "where ny.paymentdate = '{$paymentdate}' ";

        if (!empty($businesstype)) {
            $sql .= " and yw.businesstype = '" . $businesstype . "' ";
        }
        if (!empty($idnumber)) {
            $sql .= " and yw.idnumber = '" . $idnumber . "' ";
        }

        if (!empty($taobaoname)) {
            $sql .= " and ny.taobaoname = '" . $taobaoname . "' ";
        }

        if (!empty($newjoin_status)) {
            $sql .= " and ny.newjoin_status = '" . $newjoin_status . "' ";
        }
        if (!empty($payingcompany)) {
            $sql .= " and ny.payingcompany = '" . $payingcompany . "' ";
        }
        if (!empty($paymentnumber)) {
            $sql .= " and yw.paymentnumber = '" . $paymentnumber . "' ";
        }

        if (!empty($refundornot)) {
            $sql .= " and ny.refundornot = '" . $refundornot . "' ";
        }

//        $sql .= " and ny.refundornot <> '已退款' ";
        $sql .= " and ( (ny.newjoin_status = '办理失败' and ny.refundornot = '') or ny.refundornot = '需退款') ";

        $total = Db::query($sql1 . $sql);

        $sqlXX = $sql0 . $sql;
        $count = $total[0]['num'];

        $fileName = "问题客户[" . $paymentdate . "]";

        set_time_limit(0);
        ini_set('memory_limit', '-1');

        header('Content-Type: application/vnd.ms-execl');
        header('Content-Disposition: attachment;filename="' . $fileName . '.csv"');

        //打开php标准输出流
        //以写入追加的方式打开
        $fp = fopen('php://output', 'a');

        //一次取5000条数据
        $step = ceil($count/5000);

        $title = array('业务类型', '缴纳年月', '淘宝名称', '身份证号', '客户姓名', '客户电话', '缴纳号码', '缴纳公司', '缴纳基数', '缴纳金额', '付代理费', '新入备注', '问题备注', '退款状态', '审核员');

        //将标题写到标准输出中
        fputcsv($fp, $title);

        for($s = 0; $s < $step; $s++) {
            $page_count = $s;
            $page_size = 5000;
            $offset = $page_count * $page_size;
            $data = Db::query($sqlXX." limit 5000 offset ".$offset);
            foreach ($data as &$row) {

                $v = array(
                    $row['businesstype'],
                    "\t" .$row['paymentdate'],
                    $row['taobaoname'],
                    "\t" .$row['idnumber'],
                    $row['kehuname'],
                    $row['kehumobile'],
                    "\t" .$row['paymentnumber'],
                    $row['payingcompany'],
                    $row['paymentbase'],
                    $row['paymentamount'],
                    $row['agencyfees'],
                    $row['newjoin_note'],
                    $row['wenti_note'],
                    $row['refundornot'],
                    $row['auditman']
                );

                fputcsv($fp, $v);
            }
            //每5000条数据就刷新缓冲区
            ob_flush();
            flush();
        }
        exit();
    }

    public function sendmsg0($mobile, $businesstype, $name) {

//        $mobile = 13564508883;

        if ($businesstype == '公积金') {
            $message = "尊敬的" . $name . "客户，感谢您选择上海社保通代缴业务，公积金业务代缴已经完成请于次月15-20日公积金官网查询。";
        } else if ($businesstype == '社保') {
            $message = "尊敬的" . $name . "客户，感谢您选择上海社保通代缴业务，社保业务代缴已经完成请于次月15-20日公积金官网查询。";
        } else {
            return;
        }

        $url = "http://jk.106api.cn/smsUTF8.aspx";
        $params = array(
            "type" => "send",
            "username" => "465979204",
            "password" => "A7D2D07BEEA0BB4025E6C9A804D31707",
            "gwid" => "daef24b",
            "mobile" => $mobile,
            "message" => $message,
        );
//        $data = Http::post($url, $params);


//        echo $data;
//        $ret = json_decode($data, TRUE);
//        if ($ret['result']) {
//            echo $ret['result'];
//        }

    }

    public function sendmsg($mobile, $businesstype, $name) {

//        $mobile = 13564508883;

        if ($businesstype == '公积金') {
            $message = "尊敬的" . $name . "客户，感谢您选择我司代缴社保公积金。公积金代缴业务已办理成功，请次月15至20日于公积金官网查询。";
        } else if ($businesstype == '社保') {
            $message = "尊敬的" . $name . "客户，感谢您选择我司代缴社保公积金。社保代缴业务已办理成功，请次月10至15日于就近社保中心查询。";
        } else {
            return;
        }


        $target = "http://106.ihuyi.com/webservice/sms.php?method=Submit";
        $post_data = "account=C84233857&password=c03d241ab20893478876564c8c67d219&mobile=" . $mobile . "&content=" . rawurlencode($message);
//查看用户名 登录用户中心->营销短信>产品总览->API接口信息->APIID
//查看密码 登录用户中心->营销短信>产品总览->API接口信息->APIKEY
        $gets = $this->xml_to_array($this->Post($post_data, $target));


    }

    //转出
    public function sendmsg2($mobile, $businesstype, $name) {

//        $mobile = 13564508883;

        if ($businesstype == '公积金') {
            $message = "尊敬的" . $name . "客户，感谢您选择我司代缴社保公积金。特此通知：您的公积金封存或转出业务已办理完成。祝您工作顺利，生活愉快！";
        } else if ($businesstype == '社保') {
            $message = "尊敬的" . $name . "客户，感谢您选择我司代缴社保公积金。特此通知：您的社保转出业务已办理完成。祝您工作顺利，生活愉快！";
        } else {
            return;
        }


        $target = "http://106.ihuyi.com/webservice/sms.php?method=Submit";
        $post_data = "account=C84233857&password=c03d241ab20893478876564c8c67d219&mobile=" . $mobile . "&content=" . rawurlencode($message);
//查看用户名 登录用户中心->营销短信>产品总览->API接口信息->APIID
//查看密码 登录用户中心->营销短信>产品总览->API接口信息->APIKEY
        $gets = $this->xml_to_array($this->Post($post_data, $target));


    }

    function Post($curlPost, $url)
    {
        header("Content-type:text/html; charset=UTF-8");

        $curl = curl_init();
        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_HEADER, false);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($curl, CURLOPT_NOBODY, true);
        curl_setopt($curl, CURLOPT_POST, true);
        curl_setopt($curl, CURLOPT_POSTFIELDS, $curlPost);
        $return_str = curl_exec($curl);
        curl_close($curl);
        return $return_str;
    }

    function xml_to_array($xml)
    {
        $reg = "/<(\w+)[^-->]*>([\\x00-\\xFF]*)<\\/\\1>/";
        if (preg_match_all($reg, $xml, $matches)) {
            $count = count($matches[0]);
            for ($i = 0; $i < $count; $i++) {
                $subxml = $matches[2][$i];
                $key = $matches[1][$i];
                if (preg_match($reg, $subxml)) {
                    $arr[$key] = xml_to_array($subxml);
                } else {
                    $arr[$key] = $subxml;
                }
            }
        }
        return $arr;
    }

    //设置权限记录
    function setquanxianlog($params)
    {
        if (!$params) {
            return $params;
        }
        $audit = $this->auth->check('dahui/dingdan/audit');
        $luru = $this->auth->check('dahui/dingdan/luru');
        $caozuo = $this->auth->check('dahui/dingdan/caozuo');
        $admin = $audit;

        if ($audit) {
            //审核员和管理员
            $params['auditman'] = $this->auth->nickname;
        } else if ($luru) {
            $params['luruman'] = $this->auth->nickname;
        } else if ($caozuo) {
            $params['opman'] = $this->auth->nickname;
        }

        return $params;
    }

    function setquanxianlog2($row)
    {
        if (!$row) {
            return $row;
        }
        $audit = $this->auth->check('dahui/dingdan/audit');
        $luru = $this->auth->check('dahui/dingdan/luru');
        $caozuo = $this->auth->check('dahui/dingdan/caozuo');
        $admin = $audit;

        if ($audit) {
            //审核员和管理员
            $row->auditman = $this->auth->nickname;
        } else if ($luru) {
            $row->luruman = $this->auth->nickname;
        } else if ($caozuo) {
            $row->opman= $this->auth->nickname;
        }

        return $row;
    }


}